package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Iterables;
import com.google.common.collect.Maps;
import com.iris.live.services.common.ApprovalUtils;
import com.iris.live.services.common.DateUtils;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.Lsh先享后选设置Model;
import com.iris.live.services.data.repositories.FinanceRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.FinanceService;
import com.iris.live.services.services.FinanceService.GetHotLeverByConditionRequest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.text.DateFormat;
import java.util.*;

/**
 * Created by sxy on 2016/7/28
 */
@Repository
@Transactional//(readOnly = true)
public class FinanceRepositoryImpl extends BaseRepository implements FinanceRepository {
    private static final Logger logger = LoggerFactory.getLogger(FinanceRepositoryImpl.class);

    /**
     * 金融业务数据
     */
    // 金融主界面
    @Override
    public PageData getFinanceInfoList(int pageSize, int pageIndex, String _permit, String permit,
                                       String username, String userPhone, String financeCompany, String financeStatus,
                                       String financeProduct) {
        String sql = "select a.更新时间 as '创建日期', b.名称 as '客户姓名', b.电话 as '客户电话', a.客户类型, c.状态, c.状态 \n" +
                ", d.热销级别 as '意向产品热销级别', d.金融机构 as '意向金融机构', d.贷款期数 as '贷款期限', d.贷款比例, d.金融利率 as '意向利率' \n" +
                ", e.人员姓名 as '销售顾问', e.人员电话 as '销售顾问电话', f.级别 as '意向车系', f.车型 as '意向车型', a.当前状态 as '新车销售状态' ,a.顾客编号, a.报价编号 from 报价单表 a \n" +
                "join 报价客户表 b on a.顾客编号 = b.编号 \n" +
                "join Lsh金融申请 c on a.金融 = c.金融编号 \n" +
                "join 报价金融表 d on a.金融 = d.金融编号 \n" +
                "join 用户 e on a.销售顾问 = e.用户名 \n" +
                "join LSH车型 f on a.车型编号 = f.车型编号 \n" +
                "where 1 = 1 \n";
        Map params = Maps.newHashMap();
//        params.put("_permit", _permit);
        if (!Strings.isNullOrEmpty(permit)) {
            sql += "and a.许可 like :permit \n";
            params.put("permit", "%" + permit + "%");
        }

        if (!Strings.isNullOrEmpty(username)) {
            sql += "and b.名称 like :username \n";
            params.put("username", "%" + username + "%");
        }

        if (!Strings.isNullOrEmpty(userPhone)) {
            sql += "and b.电话 like :userPhone \n";
            params.put("userPhone", "%" + userPhone + "%");
        }

        if (!Strings.isNullOrEmpty(financeCompany)) {
            sql += "and d.金融机构 = :financeCompany \n";
            params.put("financeCompany", financeCompany);
        }

        if (!Strings.isNullOrEmpty(financeStatus)) {
            sql += "and (c.状态 = :status or c.状态 = :financeStatus) \n";
            params.put("financeStatus", financeStatus);
            params.put("status", financeStatus);
        }

        sql += "order by a.更新时间";
        return this.getPage(sql, params, pageSize, pageIndex);
    }


    //金融产品设置
    @Override
    public PageData getFinanceProductList(int pageSize, int pageIndex, String permit,
                                          String productType, Date sDate, Date eDate, String startAnnualRate,
                                          String endAnnualRate, String code, String isValid, String permissionCode, String period, String anJieType, String IsStandard ,
                                          String brand ,int isDealer,String parentPermit) {
        String headsql = "  select  * from ( ";
        String endsql="  )  as b  where 1=1 ";
        String sql = "select  \n" +
                "  a.金融编号,   \n" +
                "  a.产品编码 as '金融产品编码',   \n" +
                "  a.金融产品名称,   \n" +
                "  a.许可 as '创建方',   \n" +
                "  a.产品分类 as '产品类型',   \n" +
                "  a.贴息类型 as '贴息方',   \n" +
                "  a.SI编码," +
                "  c.id areaId," +
                "  c.area_name areaName," +
                "  d.id storeId," +
                "  d.area_name storeName,   \n" +
                "  ifnull((select hot_rank from finance_product_hot_rank where fi_id = a.金融编号 and permit = '" + permit + "'), a.热销级别)  as '优先推荐',   \n" +
                "  b.金融机构,   \n" +
                "  a.开始日期 as '生效日期',   \n" +
                "  a.结束日期 as '终止日期',   \n" +
                "  CASE  \n" +
                "    WHEN CURDATE()>=a.开始日期 and CURDATE()<=a.结束日期  THEN '有效'   \n" +
                "    ELSE '无效'   \n" +
                "  END AS '产品状态',  \n" +
                "  a.是否尾款 as '尾款',   \n" +
                "  a.最低首付比例,   \n" +
                "  a.保证金比例 as '保证金',   \n" +
                "  a.期数 as '期限',   \n" +
                "  a.最高尾款比例,   \n" +
                "  a.年利率 as '客户利率',   \n" +
                "  a.客户类别,   \n" +
                "  a.贷款类型,   \n" +
                "  a.备注,   \n" +
                "  a.更新账号,   \n" +
                "  a.更新时间,  \n" +
                "  a.金融系列   \n" +
                "from LSH金融设置 a   \n" +
                "join LSH金融机构设置 b on a.金融机构编号 = b.金融机构编号" +
                "  left join group_area c on a.suitable_area = c.id   " +
                "  left join group_area d on a.suitable_store = d.id  " +
                "  left join group_area e on a.许可 = e.permit  " +
                "where 1 = 1 and (a.是否删除 <> '是') ";
        Map<String, Object> params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(anJieType)) {
            sql += "and a.按揭类型 =:anJieType \n";
            params.put("anJieType", anJieType);
        }
        if (!Strings.isNullOrEmpty(productType)) {
            sql += "and a.产品分类 = :productType \n";
            params.put("productType", productType);
        } else if (isDealer==0) { // 经销商端好像没有分贴息费贴息
            sql += " and a.产品分类 <> '贴息产品' \n";
        }

        if(!Strings.isNullOrEmpty(brand)){
            sql += " and a.brand = :brand ";
            params.put("brand", brand);
        }

        if(sDate != null && !"".equals(sDate.toString())&&eDate != null && !"".equals(eDate.toString())){
            sql+="AND (" +
                    "    (" +
                    "      a.开始日期 <= :startDate " +
                    "      AND a.结束日期 >= :endDate  " +
                    "    ) " +
                    "    OR (" +
                    "      a.开始日期 <= :startDate " +
                    "      AND a.结束日期 >= :startDate  " +
                    "    ) " +
                    "    OR (" +
                    "      a.开始日期 <= :endDate  " +
                    "      AND a.结束日期 >= :endDate" +
                    "    ) " +
                    "    OR (" +
                    "      a.开始日期 >= :startDate " +
                    "      AND a.结束日期 <= :endDate " +
                    "    )" +
                    "  ) ";
            params.put("startDate", sDate);
            params.put("endDate", eDate);

        }else {
            if(sDate != null && !"".equals(sDate.toString())){
                sql += " and (a.开始日期 >= :startDate  or  a.结束日期>=:startDate )   \n";
                params.put("startDate", sDate);

            }else if(eDate != null && !"".equals(eDate.toString())){
                sql += " and (a.结束日期 <= :endDate or a.开始日期<= :endDate  )   \n";
                params.put("endDate", eDate);
            }
        }

       /* if (startDate != null && "".equals(startDate.toString())) {
            sql += "and a.开始日期 <= :startDate   and a.结束日期 >=:startDate   \n";
            params.put("startDate", startDate);
        }

        if (endDate != null && "".equals(endDate.toString())) {
            sql += "and a.结束日期 >= :endDate \n";
            params.put("endDate", endDate);
        }*/

        if (!Strings.isNullOrEmpty(startAnnualRate)) {
            sql += "and cast(年利率 as DECIMAL(10,4)) >= cast("+startAnnualRate+" as DECIMAL(10,4)) \n";
        }

        if (!Strings.isNullOrEmpty(endAnnualRate)) {
            sql += "and cast(年利率 as DECIMAL(10,4)) <= cast("+endAnnualRate+" as DECIMAL(10,4)) \n";
        }

        //code就是金融机构(编码)
        if (!Strings.isNullOrEmpty(code)) {
            sql += " and a.金融机构编号 = :code \n";
            params.put("code", code);
        }

        if (!Strings.isNullOrEmpty(isValid)) {
            java.sql.Timestamp  date=new  java.sql.Timestamp(new  Date().getTime());
            if(isValid.equals("有效")){
              /*  endsql+= "and b.开始日期 <= :date \n";
                endsql += "and b.结束日期 >= :date \n";
                params.put("date", date);*/
                endsql += "and b.`产品状态` = :isValid \n";
                params.put("isValid", "有效");
            }
            else if (isValid.equals("无效")){
              /*  endsql+= "and b.开始日期 > :date \n";
                endsql += "and b.结束日期 < :date \n";
                params.put("date", dateb.);*/
                endsql += "and b.`产品状态` <> :isValid \n";
                params.put("isValid", "有效");
            }
        }

        /*if (!Strings.isNullOrEmpty(permissionCode)) {
            params.put("permissionCode", permissionCode);*/
        if(isDealer == 0){ // 集团端登录
            sql+=   "   and   e.is_dealer <> 1 \n";
            if(!"LSH".equals(permit)){ //大区登录
                params.put("permit", permit);
                sql += " and (c.permit is null or c.permit = '' or c.permit = :permit) ";
            }
        }else{ // 经销商端登录
            params.put("permit", permit);
            if (!"LSH".equals(permissionCode)) { //经销商登录查看自己的
                sql += "   and   a.许可 = :permit\n";
            } else { // 经销商登录查看集团的
                sql += "   and e.is_dealer <> 1 and  (d.permit = :permit or (c.permit = :parentPermit and (d.permit is null or d.permit = ''))) ";
                params.put("parentPermit",parentPermit);
            }
        }
       /* } else {
            sql += " and (a.许可 ='LSH' OR a.许可=:permit)\n";
            params.put("permit", permit);
        }*/
        if (!Strings.isNullOrEmpty(period)) {
            sql += "and a.期数 = :period \n";
            params.put("period", period);
        }
        sql += "order by a.更新时间 desc";
        sql=headsql+sql+endsql;
        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public PageData getFinanceOrg(int pageSize, int pageIndex, String permit, String permissionCode,String parentName) {
        String sql = "select a.金融机构编号,  " +
                "a.金融机构,  " +
                "a.状态, " +
                "a.brand 品牌, " +
                "a.applicable_area 区域许可, " +
                "a.applicable_store 经销店许可, " +
                "e.dealer_name 区域, " +
                "f.dealer_name 经销店, " +
                "c.id 金融机构类型编号, " +
                "c.name 金融机构类型,  " +
                "a.更新账号,  " +
                "a.更新时间,  " +
                "a.许可," +
                "b.金融机构手续费编号 ,  " +
                "b.固定费率 手续费率 ," +
                "b.固定手续费  " +
                "from lsh金融机构设置 a  " +
                "LEFT JOIN LSH金融机构手续费 b on a.金融机构编号= b.金融机构编号  " +
                "LEFT JOIN 金融机构名称表 c on a.type = c.id " +
                "left join group_area e on a.applicable_area = e.permit  " +
                "left join group_area f on a.applicable_store = f.permit " +
                "left join group_area g on a.许可 = g.permit " +
                "where 1=1 " ;

        Map params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(permit)&&!ApprovalUtils.GROUP_PERMIT.equals(permit)) {
            sql += "and a.许可 in( :permissionCode ,:parentName ,'LSH') ";
            params.put("permissionCode", permit);
            params.put("parentName", parentName);
        } else { // 集团
            sql += " and g.is_dealer <> 1 ";
        }
        sql +=" order by c.name,a.更新时间 desc";
        return this.getPage(sql, params, 0, pageIndex);
    }

    @Override
    public List getFinanceOrg(String permissionCode,String areaPermit) {
        String sql = "select 金融机构编号,金融机构,状态, 更新账号, 更新时间,a.许可,a.applicable_area 适用区域,applicable_store 适用单店 from LSH金融机构设置 a \n" +
                "where 1 = 1 \n";
        Map params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(permissionCode)) {
            sql += "and 许可 in ( :permissionCode,:areaPermit, 'LSH') \n";
            params.put("permissionCode", permissionCode);
            params.put("areaPermit",areaPermit);
        } else {
            sql += " and 许可 = 'LSH' \n";
        }
        return this.getList(sql, params);
    }

    @Override
    public List getApplicableFinanceOrgByArea(String permissionCode) {
        String sql = "select 金融机构编号,金融机构,状态, 更新账号, 更新时间,a.许可 from LSH金融机构设置 a left join group_area b on a.许可 = b.permit   \n" +
                "where b.is_dealer <> 1\n";
        if (!Strings.isNullOrEmpty(permissionCode)&&!ApprovalUtils.GROUP_PERMIT.equals(permissionCode)) { //集团许可能看所有大区的数据
            sql += " and (a.applicable_area = '' or a.applicable_area is null or  a.applicable_area = :permissionCode) and a.许可 in (:permissionCode ,'LSH') ";
            Map params = Maps.newHashMap();
            params.put("permissionCode",permissionCode);
            return this.getList(sql, params);
        }
        return this.getList(sql);
    }


    @Override
    public List getFinancePoundageList(int financeCode) {
        String sql = "select " +
                "a.金融机构手续费编号, " +
                "a.是否固定, " +
                "a.分类, " +
                "a.集团固定手续费, " +
                "a.集团固定费率, " +
                "a.固定手续费, " +
                "a.固定费率, " +
                "a.更新账号, " +
                "a.更新时间 " +
                "from LSH金融机构手续费 a \n" +
                //"left join LSH金融机构设置 b on a.金融机构编号 = b.金融机构编号\n" +
                "where 1 = 1 \n";
        Map params = Maps.newHashMap();
        if (financeCode != 0) {
            sql += "and a.金融机构编号 = :financeCode \n";
            params.put("financeCode", financeCode);
        }
        return this.getList(sql, params);
    }

    @Override
    public List getFinancePoundageList_Ex(int orgCode,
                                          String permissionCode) {
        String sql = "select 金融季度额外奖励编号,起始范围,结束范围,额外奖励 from LSH金融季度额外奖励 \n" +
                "where 金融机构编号 = :orgCode \n";
        Map params = Maps.newHashMap();
        params.put("orgCode", orgCode);
        if (!Strings.isNullOrEmpty(permissionCode)) {
            sql += "and 许可 = :permissionCode \n";
            params.put("permissionCode", permissionCode);
        }
        return this.getList(sql, params);
    }

    @Override
    public PageData getFirstUseProductList(int pageSize, int pageIndex, String permit,
                                           String brand, String carCode, String carDetail, Date startDate, Date endDate,
                                           String startAnnualRate, String endAnnualRate, String param, String permissionCode) {
        String sql = "select a.金融编号, a.开始日期, a.结束日期, a.是否有效, a.最低首付比例, a.保证金比例 \n" +
                ", a.期数, c.品牌, c.车型, c.车型详细, c.MSRP, a.更新账号, a.更新时间,a.备注 from LSH金融设置 a \n" +
                "join LSH金融设置适用车型 b on a.金融编号 = b.金融编号 \n" +
                "join LSH车型 c on c.车型编号 = b.适用车型 \n" +
                "where a.按揭类型 =  '先享后选' \n";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(brand)) {
            sql += "and c.品牌 = :brand \n";
            params.put("brand", brand);
        }

        if (!Strings.isNullOrEmpty(carCode)) {
            sql += "and c.车型编号 = :carCode \n";
            params.put("carCode", carCode);
        }

        if (!Strings.isNullOrEmpty(carDetail)) {
            sql += "and c.车型详细 = :carDetail \n";
            params.put("carDetail", carDetail);
        }

        if (startDate != null) {
            sql += "and a.开始日期 >= :startDate \n";
            params.put("startDate", DateFormat.getDateInstance().format(startDate));
        }

        if (endDate != null) {
            sql += "and a.结束日期 <= :endDate \n";
            params.put("endDate", DateFormat.getDateInstance().format(endDate));
        }

        if (startAnnualRate != null) {
            sql += "and a.年利率 >= :startAnnualRate \n";
            params.put("startAnnualRate", startAnnualRate);
        }

        if (endAnnualRate != null) {
            sql += "and a.年利率 <= :endAnnualRate \n";
            params.put("startAnnualRate", endAnnualRate);
        }

        if (!Strings.isNullOrEmpty(permissionCode)) {
            sql += "and a.许可 = :permissionCode \n";
            params.put("permissionCode", permissionCode);
        }

        if (!Strings.isNullOrEmpty(param)) {
            sql += "and exists ( select 1 from LSH先享后选设置 s where s.参数 = :param and s.金融编号 = a.金融编号) \n";
            params.put("param", param);
        }
        sql += "order by a.更新时间 desc";
        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public List getFirstUseProductDetail(int financeCode) {
        String sql = "select 是否固定,年利率,保值金额,参数 from LSH先享后选设置 where 金融编号 = :financeCode";
        Map map = new HashMap();
        map.put("financeCode", financeCode);
        return this.getList(sql, map);
    }

    @Override
    public PageData getFailReasonList(String permit, String permissionCode, int pageSize, int pageIndex) {
        String sql = "select a.金融战败原因编号,a.战败原因, a.创建时间, a.更新时间, a.启用状态,b.战败内容,b.战败分类 from LSH金融战败原因 a \n" +
                "left join LSH金融战败原因附属 b on b.金融战败原因编号 = b.金融战败原因编号 where 1 = 1 \n";
        if (!Strings.isNullOrEmpty(permissionCode)) {
            sql += " and a.许可 in (:permissionCode,'LSH') \n";
        } else {
            sql += " and a.许可 = 'LSH' \n";
        }
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("permissionCode", permissionCode);
        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public PageData getPlanList(String permit, String orderId) {
        String sql = "select '方案1', b.* from 报价单表 a \n" +
                "join 报价金融表 b on a.金融A = b.金融编号 \n" +
                "where a.报价编号 = :orderId \n" +
                "UNION ALL \n" +
                "select '方案2', b.* from 报价单表 a \n" +
                "join 报价金融表 b on a.金融B = b.金融编号 \n" +
                "where a.报价编号 = :orderId \n" +
                "UNION ALL \n" +
                "select '方案3', b.* from 报价单表 a \n" +
                "join 报价金融表 b on a.金融C = b.金融编号 \n" +
                "where a.报价编号 = :orderId \n";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("orderId", orderId);
        List data = this.getList(sql, params);
        return new PageData(data, data.size());
    }

    @Override
    public Object getFinanceInfoByID(int id) {
        String sql = "select b.名称, b.电话, a.客户类型, c.状态, c.状态 \n" +
                ", d.热销级别, d.金融机构, d.贷款期数, d.贷款比例, d.金融利率 \n" +
                ", e.人员姓名, e.人员电话, f.级别, f.车型,a.车型编号, a.当前状态,a.顾客编号,a.报价编号,g.顾客性别\n" +
                ",a.折后价,a.许可,a.金融战败原因编号,d.更新时间,a.金融所属经销商,c.申请结果日期,c.抵押完成日期\n" +
                ",c.准全款原因 ,c.申请日期,h.经销商名,i.经销商名 as 金融所属经销商名, d.金融编号 from 报价单表 a \n" +
                "join 报价客户表 b on a.顾客编号 = b.编号 \n" +
                "join Lsh金融申请 c on a.金融 = c.金融编号 \n" +
                "join 报价金融表 d on a.金融 = d.金融编号 \n" +
                "join 用户 e on a.销售顾问 = e.用户名 \n" +
                "join LSH车型 f on a.车型编号 = f.车型编号\n" +
                "join 顾客记录汇总 g on a.顾客编号 = g.顾客编号 \n" +
                "join LSH经销商表 h on a.许可 = h.许可_BMBS \n" +
                "join LSH经销商表 i on a.金融所属经销商 = i.许可_BMBS \n" +
                "where a.报价编号 = :id";
        Map params = Maps.newHashMap();
        params.put("id", id);
        List data = this.getList(sql, params);
        return Iterables.getFirst(data, null);
    }

    /**
     * 适用大昌行多品牌需求
     * 1.集团A品牌,经销商无:不展示
     * 2.集团AB ,经销商A,展示适用车型为A
     * @param id
     * @param brands
     * @return
     */
    @Override
    public List getProductSuitableCarListFilterModel(int id,List<String> brands,boolean permitFlag) {
        StringBuffer sql = new StringBuffer("select 来源,级别,车型,车型详细,车型编号,品牌 from LSH金融设置适用车型 r\n" +
                "inner join LSH车型 c on r.`适用车型` = c.`车型编号`\n" +
                "where r.金融编号 = :id ");
        Map params = Maps.newHashMap();
        params.put("id", id);
        if(permitFlag){ // 如果为集团许可,则直接返回所有数据
            return this.getList(sql.toString(), params);
        }
        if(brands==null||brands.size()==0) {
            return null;
        }
        sql.append(" and c.品牌 in (''");
        for(String brand:brands){
            sql.append(",'");
            sql.append(brand);
            sql.append("'");
        }
        sql.append(")");
        return this.getList(sql.toString(), params);
    }

    @Override
    public List getProductSuitableCarList(int id) {
        String sql = "select 来源,级别,车型,车型详细,车型编号,品牌 from LSH金融设置适用车型 r\n" +
                "inner join LSH车型 c on r.`适用车型` = c.`车型编号`\n" +
                "where r.金融编号 = :id";
        Map params = Maps.newHashMap();
        params.put("id", id);
        List data = this.getList(sql, params);
        return data;
    }

    @Override
    public Map getStatisticsNumber(String permissionCode) {
        String sql = "select sum(case when a.状态 = '01' then 1 else 0 end) 待报价,  \n" +
                " sum(case when a.状态 = '03' then 1 else 0 end) 准全款潜客,   \n" +
                " sum(case when a.状态 = '03' or a.状态 = '01'    " +
                "  or a.状态 = '05' then 1 else 0 end) 业务待处理个数    \n" +
                "from Lsh金融申请 a    \n" +
                "join LSH金融设置 b on a.金融编号 = b.金融编号   \n" +
                "where b.许可 = :permissionCode   ";
        Map params = Maps.newHashMap();
        params.put("permissionCode", permissionCode);
        List data = this.getList(sql, params);
        return data.size() > 0 ? (Map) data.get(0) : null;
    }

    @Override
    public List getFinanceProductListBySeries(String permit, String series) {
        String sql = "select a.金融编号,a.金融机构编号, a.产品编码 as '金融产品编码',"
                + "a.业务类型, a.是否固定, a.利率状态,"
                + "  c.id areaId,"
                + "  c.area_name areaName,"
                + "  d.id storeId,"
                + "  d.area_name storeName,   \n"
                + "a.金融产品名称, a.许可 as '创建方', a.产品分类 as '产品类型',"
                + "a.贴息类型 as '贴息方', a.SI编码, " +
                "ifnull((select hot_rank from finance_product_hot_rank where fi_id = a.金融编号 and permit = '" + permit + "'), a.热销级别) as '优先推荐', "
                + "b.金融机构,  a.开始日期 as '生效日期', a.结束日期 as '终止日期', "
                + "a.保证金比例 as '保证金', a.期数 as '期限', a.最高尾款比例, a.年利率 as '客户利率', "
                + "a.客户类别, a.贷款类型, a.备注, a.更新账号, a.更新时间,a.金融系列,a.按揭类型,"
                + "case when  a.是否有效  is null then '' else a.是否有效  end  as '产品状态', "
                + "a.是否尾款  as '尾款', "
                + "a.最低首付比例, "
                + "a.贴息利率,a.贴息利率B from LSH金融设置 a \n"
                + " left join LSH金融机构设置 b on a.金融机构编号 = b.金融机构编号 \n"
                + "  left join group_area c on  a.suitable_area = c.id   "
                + "  left join group_area d on a.suitable_store = d.id   "
                + "where a.金融系列 = :series";
        Map params = new HashMap();
        params.put("series", series);
        List list = this.getList(sql,params);
        for (int i = 0; i < list.size(); i++) {
            String id = ((Map) list.get(i)).get("金融机构编号").toString();
            sql = "select 固定手续费,是否固定,固定费率,集团固定手续费,集团固定费率 from LSH金融机构手续费 where 金融机构编号 = :id";
            params.clear();
            params.put("id", id);
            ((Map) list.get(i)).put("手续费", this.getList(sql, params));
        }
        return list;
    }


    @Override
    public List getFinaceProUpdateInfo(String permit) {
        StringBuffer queryCondition = new StringBuffer("");

        if (!Strings.isNullOrEmpty(permit)) {
            String[] permitArr = permit.split(",");

            int i = 0;
            for (String p : permitArr) {
                if (i == 0) {
                    queryCondition.append("许可 = '" + p + "' ");
                } else {
                    queryCondition.append(" OR 许可 = '" + p + "'");
                }
            }
        }

        String sqlStr = "SELECT `更新时间`,`更新账号` "
                + "FROM `LSH金融设置` "
                + "WHERE " + queryCondition.toString() + " ORDER BY `更新时间` DESC LIMIT 1";
        return this.getList(sqlStr);
    }


    @Override
    public List getFinaceInstitutionPoundage(String permit) {
        StringBuffer queryCondition = new StringBuffer("  " +
                " SELECT " +
                " a.金融机构, " +
                " b.是否固定, " +
                " b.`固定手续费`, " +
                " b.`固定费率`, " +
                " b.分类 " +
                " FROM " +
                " `LSH金融机构设置` AS a " +
                " LEFT JOIN `LSH金融机构手续费` AS b ON a.`金融机构编号` = b.`金融机构编号` " +
                " WHERE " +
                " a.`状态` = '启用' " +
                " AND ( " +
                " a.`许可` = 'LSH' " +
                " OR a.许可 = :permit " +
                ") ");

        Map params=Maps.newHashMap();
        params.put("permit",permit);


	/*	if(!Strings.isNullOrEmpty(permit)){
            String[] permitArr = permit.split(",");

			int i = 0 ;
			for(String p : permitArr){
				if(i == 0){
					queryCondition.append("许可 = '"+p+"' ");
				}else{
					queryCondition.append(" OR 许可 = '"+p+"'");
				}
			}
		}

		String sqlStr = "SELECT a.金融机构,b.是否固定,b.`固定手续费`,b.`固定费率`,b.分类 "
				+ 		"FROM `LSH金融机构设置` as a "
				+		"LEFT JOIN `LSH金融机构手续费` as b ON a.`金融机构编号`=b.`金融机构编号` "
				+ 		"WHERE a.`状态`='启用' AND (  a.`许可` = 'LSH'    "+queryCondition.toString()+" ) ";*/
        return this.getList(queryCondition.toString(), params);
    }


    @Override
    public List getFinaceCodeByFinaceProCode(String proCode, String permit) {
        Map params = new HashMap();

        String sqlStr = "SELECT `金融编号` "
                + "FROM `LSH金融设置` "
                + "WHERE `产品编码`=:proCode AND `许可`=:permit "
                + "AND IFNULL(`是否删除`,'') <> '是' LIMIT 1";

        params.put("proCode", proCode);
        params.put("permit", permit);
        return this.getList(sqlStr, params);
    }


    @Override
    public List getFirstUseProUpdateInfo(String permit) {
        Map params = new HashMap();

        String sqlStr = "SELECT `更新时间`,`更新账号` "
                + "FROM `LSH金融设置` "
                + "WHERE `许可`=:permit AND `按揭类型`='先享后选' "
                + "ORDER BY `更新时间` DESC LIMIT 1";

        params.put("permit", permit);

        return getList(sqlStr, params);
    }


    @Override
    public PageData getFirstUseProduct(FinanceService.GetFirstUseProductListRequest request) {
        Map params = new HashMap();

        StringBuffer sqlBuffer =
                new StringBuffer("SELECT d.`品牌`,d.`级别`, d.`车型`, d.`车型详细`,"
                        + "`开始日期`,`结束日期`,是否有效 产品状态,d.MSRP,"
                        + "a.`最低首付比例`,a.`保证金比例`,b.`期数`,a.`金融编号`,"
                        + "max(CASE `参数` WHEN '15' then b.`年利率` ELSE 0 end) 万五利率,"
                        + "max(CASE `参数` WHEN '15' then `保值金额` ELSE 0 end) 万五金额,"
                        + "max(CASE `参数` WHEN '20' then b.年利率 ELSE 0 end) 二万利率,"
                        + "max(CASE `参数` WHEN '20' then `保值金额` ELSE 0 end) 二万金额,"
                        + "max(CASE `参数` WHEN '25' then b.年利率 ELSE 0 end) 二五利率,"
                        + "max(CASE `参数` WHEN '25' then 保值金额 ELSE 0 end) 二五金额,a.备注  "
                        + "FROM `LSH金融设置` AS a "
                        + "LEFT JOIN `LSH先享后选设置` AS b ON a.`金融编号`=b.`金融编号` "
                        + "LEFT JOIN `LSH金融设置适用车型` AS c ON a.`金融编号`=c.`金融编号` "
                        + "LEFT JOIN `LSH车型` AS d ON c.`适用车型`=d.`车型编号` "
                        + "WHERE `许可`='LSH' AND IFNULL(a.是否删除,'') <> '是'  AND a.按揭类型='先享后选' ");

        if (!Strings.isNullOrEmpty(request.carDetail)) {
            sqlBuffer.append(" AND d.`车型详细` = :carDetail");
            params.put("carDetail", request.carDetail);
        }

        if (!Strings.isNullOrEmpty(request.carCode)) {
            sqlBuffer.append(" AND d.`车型` = :carCode");
            params.put("carCode", request.carCode);
        }

        if (!Strings.isNullOrEmpty(request.source)) {
            sqlBuffer.append(" AND d.`来源` = :source");
            params.put("source", request.source);
        }

        if (!Strings.isNullOrEmpty(request.level)) {
            sqlBuffer.append(" AND d.`级别` = :level");
            params.put("level", request.level);
        }

        if (!Strings.isNullOrEmpty(request.param)) {
            sqlBuffer.append(" AND b.`参数` = :param");
            params.put("param", request.param);
        }

        if (!Strings.isNullOrEmpty(request.startAnnualRate)) {
            sqlBuffer.append(" AND b.`年利率` >= :startAnnualRate");
            params.put("startAnnualRate", request.startAnnualRate);
        }

        if (!Strings.isNullOrEmpty(request.endAnnualRate)) {
            sqlBuffer.append(" AND b.`年利率` <= :endAnnualRate");
            params.put("endAnnualRate", request.endAnnualRate);
        }

        if (request.startDate != null) {
            sqlBuffer.append(" AND a.`开始日期` >= :startDate");
            params.put("startDate", request.startDate);
        }

        if (request.endDate != null) {
            sqlBuffer.append(" AND a.`结束日期` <= :endDate");
            params.put("endDate", request.endDate);
        }

        sqlBuffer.append(" GROUP BY b.`金融编号` ORDER BY a.`更新时间` DESC");

        String sqlStr = sqlBuffer.toString();
        return getPage(sqlStr, params, request.getPageSize(), request.getPageIndex());
    }

    //判断所选车型是否已有相同级别
    public List getHotLeverByCondition(GetHotLeverByConditionRequest request) {
        Map params = new HashMap();
        String sql = "SELECT `热销级别` FROM LSH金融设置 as a LEFT JOIN `LSH金融设置适用车型` as b "
                + "ON a.`金融编号` = b.`金融编号` where `是否删除` <> '是' ";
        if (!Strings.isNullOrEmpty(request.permit)) {
            sql += " AND a.许可=:permit ";
            params.put("permit", request.permit);
        }
        if (!Strings.isNullOrEmpty(request.startDate.toString())) {
            sql += " AND `开始日期`<=:startDate ";
            params.put("permit", request.startDate);
        }
        if (!Strings.isNullOrEmpty(request.endDate.toString())) {
            sql += " AND `结束日期`>=:endDate ";
            params.put("permit", request.endDate.toString());
        }
        String[] hotLerver = request.hotLever.split(",", -1);
        String[] trialModel = request.trialModel.split(",", -1);
        if (hotLerver.length > 0) {
            for (int i = 0, j = hotLerver.length; i < j; i++) {
                if (i == 0) {
                    sql += " AND ( (b.`适用车型`='" + trialModel[i] + "' AND `热销级别`='" + hotLerver[i] + "')";
                } else {
                    sql += " OR (b.`适用车型`='" + trialModel[i] + "' AND `热销级别`='" + hotLerver[i] + "') ";
                }
            }
            sql += " )";
        }
        return getList(sql, params);
    }


    @Override
    public boolean checkFinanceProduceIfExit(String financeProName) {
        boolean flag;
        try {
            Map params = new HashMap();
            String sql = "SELECT 1 FROM `LSH金融设置` WHERE `金融产品名称` =:financeProName";
            params.put("financeProName", financeProName);
            List list = getList(sql, params);
            if (null != list && list.size() > 0) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
            logger.error("Check Exception",e);
            flag = false;
        }
        return flag;
    }


    @Override
    public List getMaxFinanceNum() {
        String sql = "SELECT max(`金融编号`) `金融编号` FROM `LSH金融设置` WHERE `许可`='LSH' AND `按揭类型`='先享后选'";
        return getList(sql);
    }

    @Override
    public List getMaxFinanceNumT() {
        String sql = "SELECT max(`金融编号`) `金融编号` FROM `LSH金融设置` ";
        return getList(sql);
    }


    @Override
    public List<Lsh先享后选设置Model> ifExitByNumAndSet(String canShu, Integer maxFinanceNum) {
        Map params = new HashMap();
        String sql = "select * from LSH先享后选设置  WHERE 参数=:canShu and 金融编号=:maxFinanceNum";
        params.put("canShu", canShu);
        params.put("maxFinanceNum", maxFinanceNum);
        return getList(sql, params);
    }

    @Override
    public List  getfinancialIdList(String numer,String permit){
        try{
            Map params=Maps.newHashMap();
            params.put("permit",permit);
            params.put("numer",numer);

            StringBuffer sql=new StringBuffer();
            sql.append(" SELECT `金融编号` FROM `LSH金融设置` " +
                    "  WHERE `产品编码`=:numer AND `许可`=:permit " +
                    "  AND IFNULL(`是否删除`,'') <> '是' LIMIT 1; ");
            return  this.getList(sql.toString(),params);
        }
        catch(Exception e){
            logger.error("运行异常。", e);
            return  null;
        }

    }

    //11.23
    public PageData getFinancialStandardFailList(String permit, int size, int index) {
        PageData item = null;
        Map params = Maps.newHashMap();
        StringBuffer sql = new StringBuffer();
        sql.append(" select a.* ,b.`战败分类编号`,b.`战败内容`, " +
                "  if( LENGTH(GROUP_CONCAT(b.`战败分类`)) in (9,22),'Y','N' )as 准全款,   " +
                "  if( LENGTH(GROUP_CONCAT(b.`战败分类`)) in (12,22),'Y','N' )as 汽车金融战败    " +
                "   from `lsh金融战败原因` a      " +
                " join `lsh金融战败原因附属` b     " +
                "on a.`金融战败原因编号`=b.`金融战败原因编号`    " +
                "  where   a.`许可`='lsh' or a.`许可`='LSH' or a.`许可`=:permit  GROUP BY a.`金融战败原因编号`    ");

        params.put("permit", permit);
        return this.getPage(sql.toString(), params, size, index);


    }


    public PageData getfinancialFailListByPermit(String pemrit, int size, int index) {
        PageData item = null;
        Map params = Maps.newHashMap();
        params.put("permit", pemrit);

        StringBuffer sql = new StringBuffer();
        sql.append(" select a.* ,b.`战败分类编号`,b.`战败内容`,    " +
                "  b.`战败分类` from `lsh金融战败原因` a      " +
                " join `lsh金融战败原因附属` b     " +
                "on a.`金融战败原因编号`=b.`金融战败原因编号`    " +
                "  where   (a.`许可`='lsh' or a.`许可`='LSH') or a.许可=:permit  ");

        return this.getPage(sql.toString(), params, size, index);


    }


    // 获取所有的 金融的 编号
    public List getFinaceCodeByorderlist(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);

        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT a.金融, a.`初始报价单号`, y.报价编号 FROM 报价单表 a JOIN ( SELECT max(a.`报价编号`)  " +
                "      AS 报价编号 FROM 报价单表 a WHERE a.许可 =:permit    " +
                "   GROUP BY a.`初始报价单号` ) y ON a.报价编号 = y.报价编号 WHERE a.金融 IS NOT NULL     ");

        return this.getList(sql.toString(), params);


    }

    @Override
    public PageData findfinancialDetailitemList(String permit, String city, String dealerName, String dealerLogo, String cityAvgS, String cityAvgB, Integer pageSize, Integer pageIndex, String dates) {
        Map params = Maps.newHashMap();
        StringBuffer sql = new StringBuffer("" +
                " SELECT a.许可,\n" +
                " b.大区,\n" +
                " b.小区 ,\n" +
                " b.省份,\n" +
                " b.城市,\n" +
                " b.经销商名,\n" +
                " b.许可_LSH,\n" +
                " a.经销商指导值,\n" +
                " a.集团指导值 ," +
                " c.城市平均指导值" +
                " FROM \n" +
                " `lsh金融服务费` AS a\n" +
                " JOIN `lsh经销商表` AS b ON a.许可=b.许可_BMBS" +
                " LEFT  JOIN ( \n" +
                " SELECT AVG(`经销商指导值`) 城市平均指导值 ,b.城市\n" +
                " FROM `lsh金融服务费` AS a\n" +
                " JOIN `lsh经销商表` AS b ON a.许可=b.许可_BMBS GROUP BY b.城市  ) AS c ON  c.`城市`=b.城市" +
                " where 1=1\n");
        //params.put("permit", permit);

        //城市过滤
        if (city != null && !"".equals(city)) {
            sql.append(" AND  b.城市 like:city ");
            params.put("city", "%" + city + "%");
        }
        //经销商名过滤
        if (dealerName != null && !"".equals(dealerName)) {
            sql.append(" AND b.经销商名 like:dealerName ");
            params.put("dealerName", "%" + dealerName + "%");
        }
        //经销商代码过滤
        if (dealerLogo != null && !"".equals(dealerLogo)) {
            sql.append(" AND a.许可 like:dealerLogo ");
            params.put("dealerLogo", "%" + dealerLogo + "%");
        }
        //最小值过滤
        if (cityAvgS != null && !"".equals(cityAvgS)) {
            sql.append(" AND a.经销商指导值>=:cityAvgS ");
            params.put("cityAvgS", Integer.parseInt(cityAvgS));
        }
        //最大值过滤
        if (cityAvgB != null && !"".equals(cityAvgB)) {
            sql.append(" AND a.经销商指导值<=:cityAvgB ");
            params.put("cityAvgB", Integer.parseInt(cityAvgB));
        }
        //日期过滤
        if (dates != null && !"".equals(dates)) {
            sql.append(" AND a.日期=:dates ");
            params.put("dates", dates);
        }

        return this.getPage(sql.toString(), params, pageSize, pageIndex);

    }

    @Override
    public PageData getTrackfinancialDetailitem(List<String> dates, String city, String dealerName, String dealerLogo, String cityAvgS, String cityAvgB, Integer pageSize, Integer pageIndex) {
        Map params = Maps.newHashMap();
        params.put("dateone", dates.get(0));


        StringBuffer sql = new StringBuffer(" select  " +
                "  a.大区,  \n" +
                " IF (a.`大区平均值`is not null,  a.`大区平均值`,0)  大区平均值 ,\n" +
                " a.城市,  \n" +
                " IF ( a.`城市平均值` is not null,  a.`城市平均值`,0) 城市平均值,\n" +
                " a.许可,   \n" +
                " a.经销商名,\n" +
                " IF (a.集团指导值 is not null,  a.集团指导值,0)  集团指导值,\n" +
                " IF (a.经销商指导值 is not null,  a.经销商指导值 ,0)  经销商指导值,\n" +
                " IF (a.上两月 is not null, a.上两月 ,0)  上两月,\n" +
                " IF (a.上一月 is not null,  a.上一月,0)  上一月,\n" +
                " IF (a. 本月 <>null,  a. 本月 ,0)  本月,\n" +
                " IF (a.年平均 is not null,  a.年平均 ,0)   年平均 " +
                " from( " +
                " SELECT \n" +
                " b.大区, \n" +
                " c.`大区平均值`, \n" +
                " b.城市, \n" +
                " d.`城市平均值`, \n" +
                " a.许可,  \n" +
                " b.经销商名, \n" +
                " MAX( IF( a.`日期` =:dateone , a.`集团指导值`,0 ) )  as 集团指导值, \n" +
                " MAX( IF( a.`日期` =:dateone , a.`经销商指导值`,0 ) )  as 经销商指导值, \n" +
                " MAX( IF( DATE_FORMAT(a.`日期`,'%Y-%m ')=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 2 MONTH ),'%Y-%m ') ,a.`经销商实际值`,0 ) ) 上两月, \n" +
                " MAX( IF( DATE_FORMAT(a.`日期`,'%Y-%m ')=DATE_FORMAT(date_sub(CURDATE(),INTERVAL 1 MONTH ),'%Y-%m ') ,a.`经销商实际值`,0) ) 上一月, \n" +
                " MAX( IF( DATE_FORMAT(a.`日期`,'%Y-%m ')= DATE_FORMAT(CURDATE(),'%Y-%m ')  ,a.`经销商实际值`,0) ) 本月,\n" +
                " e.`年平均` \n" +
                " FROM  \n" +
                " `lsh金融服务费` AS a \n" +
                " JOIN `lsh经销商表` AS b ON a.许可 = b.许可_BMBS \n" +
                " JOIN ( SELECT AVG(`经销商实际值`) 大区平均值, b.大区 FROM `lsh金融服务费` AS a JOIN `lsh经销商表` AS b ON a.许可 = b.许可_BMBS WHERE QUARTER(a.`日期`)= QUARTER(CURDATE())  AND YEAR(a.`日期`)= YEAR(CURDATE()) GROUP BY b.大区 ) AS c ON c.`大区` = b.`大区` \n" +
                " JOIN ( SELECT AVG(`经销商实际值`) 城市平均值, b.`城市` FROM `lsh金融服务费` AS a JOIN `lsh经销商表` AS b ON a.许可 = b.许可_BMBS where    QUARTER(a.`日期`)= QUARTER(CURDATE()) AND YEAR(a.`日期`)= YEAR(CURDATE())   GROUP BY b.城市) AS d ON d.`城市` = b.`城市` \n" +
                " JOIN ( SELECT AVG(`经销商实际值`) 年平均, a.许可 FROM `lsh金融服务费`  AS a where  YEAR(a.`日期`)= YEAR(CURDATE()) GROUP BY  a.许可 ) AS e ON e.`许可` = a.`许可`   " +
                " GROUP BY a.`许可` ) as a " +
                " where 1=1 ");


        //城市过滤
        if (city != null && !"".equals(city)) {
            sql.append(" AND  a.城市 like:city  ");
            params.put("city", "%" + city + "%");
        }
        //经销商名过滤
        if (dealerName != null && !"".equals(dealerName)) {
            sql.append(" AND a.经销商名 like:dealerName ");
            params.put("dealerName", "%" + dealerName + "%");
        }
        //经销商代码过滤
        if (dealerLogo != null && !"".equals(dealerLogo)) {
            sql.append(" AND a.许可 like:dealerLogo ");
            params.put("dealerLogo", "%" + dealerLogo + "%");
        }
        //最小值过滤
        if (cityAvgS != null && !"".equals(cityAvgS)) {
            sql.append(" AND a.经销商指导值>=:cityAvgS ");
            params.put("cityAvgS", Integer.parseInt(cityAvgS));
        }
        //最大值过滤
        if (cityAvgB != null && !"".equals(cityAvgB)) {
            sql.append(" AND a.经销商指导值<=:cityAvgB ");
            params.put("cityAvgB", Integer.parseInt(cityAvgB));
        }
        //日期过滤
 /*       if (dates != null && !"".equals(dates)) {
            sql.append(" AND a.日期=:dateone ");

        }*/

        return this.getPage(sql.toString(), params, pageSize, pageIndex);
    }


    //
    public PageData getFinancialAnnualDetailListById(String perimit, String postman, FinanceService.getfinancialFailAllListitemtwo re, int idex, int pag) {
        Map params = Maps.newHashMap();
        params.put("permit", perimit);
        StringBuffer sql = new StringBuffer();
        sql.append(" select " +
                " 	b.`顾客姓名` AS 客户姓名, " +
                " 	b.电话号码 AS 客户电话, " +
                " 	j.`value` as 新车销售状态, " +
                " 	f.`车型` 意向车型, " +
                " 	f.`车型详细` 意向车款, f.车型编号, " +
                " 	i.车架号, " +
                " 	a.折后价 车辆销售价, " +
                " 	e.经销商名 零售经销商, " +
                " 	k.`人员姓名` as 销售顾问, " +
                " 	c.热销级别, " +
                " 	c.按揭类型, " +
                " 	c.金融机构, " +
                " 	c.金融产品编码, " +
                " 	if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null,0, c.金融利率),0) as 利率, " +
                " 	if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null,0, c.金融利率),0) as 费率, " +
                " 	c.`贷款类型`, " +
                " 	c.贷款期数 as 期数, " +
                " 	c.贷款额 贷款金额, " +
                " 	c.保证金比例, " +
                " 	c.保证金金额, " +
                " 	c.首付比例, " +
                " 	c.首付额 首付金额, " +
                " 	c.尾款比例, " +
                " 	c.尾款额 尾款金额, " +
                " 	'' 还款结束期, " +
                " 	'' 还款额, " +
                " 	c.金融服务费, " +
                " 	c.金融手续费, " +
                " 	i.颜色, " +
                " 	IF(h.更新账号 is null, '', (select i.人员姓名 from 用户 i where i.用户名 =h.更新账号)) as 所属金融专员, " +
                " 	if(b.顾客性别='男','先生','女士') as 称谓, " +
                " 	'' as 备注, " +
                " 	a.报价编号, " +
                " 	c.金融编号, " +
                " 	h.初始报价单号, " +
                "   IF(x.`开票时间` IS NULL, '', DATE_FORMAT(x.`开票时间`, '%Y-%m-%d')) 开票日期, " +
                "   IF(x.`退票时间` IS NULL, '', DATE_FORMAT(x.`退票时间`, '%Y-%m-%d')) 退票日期, " +
                " 	CASE " +
                " 	WHEN h.`状态`='01' THEN '汽车金融待报价' " +
                " 	WHEN h.`状态`='02' THEN '汽车金融报价完成' " +
                " 	WHEN h.`状态`='03' THEN '准全款潜客' " +
                " 	WHEN h.`状态`='04' THEN '汽车金融战败' " +
                " 	WHEN h.`状态`='05' THEN '汽车金融意向确认' " +
                " 	WHEN h.`状态`='06' THEN '已申请-待批复' " +
                " 	WHEN h.`状态`='07' THEN '已申请-批复通过' " +
                " 	WHEN h.`状态`='08' THEN '已申请-批复未通过' " +
                " 	WHEN h.`状态`='09' THEN '通过后撤单' " +
                " 	WHEN h.`状态`='10' THEN '抵押完成' " +
                " 	WHEN h.`状态`='12' THEN '新车战败' " +
                " 	WHEN h.`状态`='11' THEN '放款到账' " +
                " 	WHEN h.`状态`='13' THEN '撤单后重新申请' " +
                " 	WHEN h.`状态`='14' THEN '条件批复' " +
                " 	ELSE '金融编辑中' END as 金融销售状态, " +
                " 	CASE " +
                " 	WHEN h.`状态`='06' THEN '待批复' " +
                " 	WHEN h.`状态`='07' THEN '批复通过' " +
                " 	WHEN h.`状态`='08' THEN '批复未通过' " +
                " 	WHEN h.`状态`='10' THEN '抵押完成' " +
                " 	WHEN h.`状态`='12' THEN '新车战败' " +
                " 	ELSE '金融销售中' END AS 审批进度 " +
                " from ( " +
                " 	SELECT " +
                " 		b.报价编号, " +
                " 		b.`初始报价单号`, " +
                " 		b.`顾客编号`, " +
                " 		b.折后价, " +
                " 		b.库存编号, " +
                " 		b.金融, " +
                " 		b.许可, " +
                " 		b.`销售顾问`, " +
                "       b.`交车`," +
                " 		b.车型编号 " +
                " 	FROM ( " +
                " 		SELECT " +
                " 			max(a.`报价编号`) AS 报价编号 " +
                " 		FROM 报价单表 a " +
                " 		WHERE a.许可 = :permit " +
                " 		AND a.金融 IS NOT NULL " +
                " 		GROUP BY a.`初始报价单号` " +
                " 	) a " +
                " 	left JOIN 报价单表 b on a.报价编号 = b.报价编号 " +
                " ) a " +
                " left join lsh库存表 i on i.库存编号 = a.库存编号 " +
                " left join `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号 " +
                " left join 报价金融表 c on a.金融 = c.金融编号 " +
                " left join 用户 k on k.用户名 = a.`销售顾问` " +
                " join (SELECT DISTINCT z.金融编号 from `报价金融详细表` z) d on d.金融编号 = c.金融编号 " +
                " left join `lsh经销商表` e on e.许可_bmbs = a.许可 " +
                " left join lsh车型 f on f.车型编号 = a.车型编号 " +
                " left join Lsh金融申请 h on h.初始报价单号 = a.初始报价单号 " +
                " LEFT JOIN car_sales_state j ON b.`新车销售状态` = j.`key` " +
                " LEFT JOIN `报价交车表` x ON a.`交车` = x.`编号`" +
                " where h.状态 not in ('00','09','07','08','11','12','13','14') " +
                " and (h.`撤单原因` is null and h.`撤单日期` is null) ");
        if (re.name != null && !"".equals(re.name)) {
            sql.append("  and  b.`顾客姓名` like  :names  ");
            params.put("names", "%" + re.name + "%");
        }
        if (re.nameTel != null && !"".equals(re.nameTel)) {
            sql.append("  and  b.电话号码 like  :tels  ");
            params.put("tels", "%" + re.nameTel + "%");
        }
        if (re.moneyorgina != null && !"".equals(re.moneyorgina)) {
            sql.append("  and  c.金融机构 like  :orgs  ");
            params.put("orgs", "%" + re.moneyorgina + "%");
        }
        if (re.solestatus != null && !"".equals(re.solestatus) && !re.solestatus.equals("0")) {
            if (re.solestatus.equals("1")) {  /*00金融编辑中		=SC未提交计算申请
                01金融未报价		=待报价
                02金融报价完成		=报价完成
                03金融准战败		=战败
                04金融战败（app不可编辑，等待PC激活）
                05金融意向确认
                06金融已申请待批复
                07金融已申请批复通过
                08金融已申请批复未通过
                09金融通过后撤单
                10金融抵押完成
                11金融放款到帐
                12新车战败*/

                sql.append("  and  h.`状态`='01'  ");
            } else if (re.solestatus.equals("2")) {

                sql.append("  and h.`状态`='02' ");
            } else if (re.solestatus.equals("3")) {

                sql.append("  and h.`状态`='03' ");
            } else if (re.solestatus.equals("4")) {

                sql.append("  and h.`状态`='05' ");
            } else if (re.solestatus.equals("5")) {

                sql.append("  and h.`状态`='04' ");


            } else if (re.solestatus.equals("6")) {

                sql.append("  and h.`状态`='06' ");
            } else if (re.solestatus.equals("7")) {

                sql.append("  and h.`状态`='07' ");
            } else if (re.solestatus.equals("8")) {

                sql.append("  and h.`状态`='08' ");
            } else if (re.solestatus.equals("9")) {

                sql.append("  and h.`状态`='10' ");
            } else {
                sql.append("  and 1=1 ");
            }

        }
        sql.append("  ORDER BY h.`更新时间` desc   ");


        return this.getPage(sql.toString(), params, pag, idex);

    }

    public PageData getFinancialBenzDetailListByUser(String permits, String uername, FinanceService.getfinancialFailAllListitemtwo re, int permit, int pa) {
        Map params = Maps.newHashMap();
        params.put("permit", permits);
        StringBuffer sql = new StringBuffer();
        sql.append(" select " +
                " 	f.车型编号, " +
                " 	b.`顾客姓名` as 客户姓名, " +
                " 	b.电话号码 as 客户电话, " +
                " 	m.`value` as 新车销售状态, " +
                " 	f.`车型` 意向车型, " +
                " 	f.`车型详细` 意向车款, " +
                " 	i.车架号, " +
                " 	a.折后价 车辆销售价, " +
                " 	e.经销商名 零售经销商, " +
                " 	k.`人员姓名` as 销售顾问, " +
                " 	c.热销级别, " +
                " 	c.按揭类型, " +
                " 	c.金融机构, " +
                " 	c.金融产品编码, " +
                " 	if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null,0, c.金融利率),0) as 利率, " +
                " 	if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null,0, c.金融利率),0) as 费率, " +
                " 	if(c.`产品类型`='0','普通','星时享') 产品类型, " +
                " 	c.贷款期数 as 合同期（月数）, " +
                " 	c.保值参数 as 年公里数, " +
                " 	c.保值比例, " +
                " 	c.`保值金额`, " +
                " 	c.保证金比例, " +
                " 	c.保证金金额, " +
                " 	c.`修养套餐金额`, " +
                " 	c.首付比例 as 预付比例, " +
                " 	c.`首付额` as 预付金额, " +
                " 	c.贷款额 as 贷款金额, " +
                " 	c.月付额 as 月供, " +
                " 	CASE " +
                " 	WHEN h.`状态`='01' THEN '汽车金融待报价' " +
                " 	WHEN h.`状态`='02' THEN '汽车金融报价完成' " +
                " 	WHEN h.`状态`='03' THEN '准全款潜客' " +
                " 	WHEN h.`状态`='04' THEN '汽车金融战败' " +
                " 	WHEN h.`状态`='05' THEN '汽车金融意向确认' " +
                " 	WHEN h.`状态`='06' THEN '已申请-待批复' " +
                " 	WHEN h.`状态`='07' THEN '已申请-批复通过' " +
                " 	WHEN h.`状态`='08' THEN '已申请-批复未通过' " +
                " 	WHEN h.`状态`='09' THEN '通过后撤单' " +
                " 	WHEN h.`状态`='10' THEN '抵押完成' " +
                " 	WHEN h.`状态`='12' THEN '新车战败' " +
                " 	WHEN h.`状态`='11' THEN '放款到账' " +
                " 	WHEN h.`状态`='13' THEN '撤单后重新申请' " +
                " 	WHEN h.`状态`='14' THEN '条件批复' " +
                " 	ELSE '汽车金融待报价' END as 金融销售状态 , " +
                " 	i.颜色, " +
                " 	IF(h.更新账号 is null, '', (select i.人员姓名 from 用户 i where i.用户名 =h.更新账号)) as 所属金融专员, " +
                " 	if(b.顾客性别='男','先生','女士') as 称谓, " +
                " 	c.金融服务费, " +
                " 	c.金融手续费, " +
                " 	'' as 备注, " +
                " 	CASE " +
                " 	WHEN h.`状态`='06' THEN '待批复' " +
                " 	WHEN h.`状态`='07' THEN '批复通过' " +
                " 	WHEN h.`状态`='08' THEN '批复未通过' " +
                " 	WHEN h.`状态`='10' THEN '抵押完成' " +
                " 	WHEN h.`状态`='12' THEN '新车战败' " +
                " 	WHEN h.`状态`='13' THEN '撤单后重新申请' " +
                " 	WHEN h.`状态`='14' THEN '条件批复' " +
                " 	ELSE '汽车金融待报价' END AS 审批进度, " +
                " 	h.初始报价单号, " +
                " 	a.报价编号, " +
                " 	c.金融编号, " +
                " IF(x.`开票时间` IS NULL, '', DATE_FORMAT(x.`开票时间`, '%Y-%m-%d')) 开票日期, " +
                " IF(x.`退票时间` IS NULL, '', DATE_FORMAT(x.`退票时间`, '%Y-%m-%d')) 退票日期, " +
                " 	h.`操作人`, " +
                " 	h.`更新时间` " +
                " from  ( " +
                " 	SELECT " +
                " 		b.报价编号, " +
                " 		b.`初始报价单号`, " +
                " 		b.`顾客编号`, " +
                " 		b.折后价, " +
                " 		b.库存编号, " +
                " 		b.金融, " +
                " 		b.许可, " +
                " 		b.`销售顾问`, " +
                "       b.`交车`," +
                " 		b.车型编号 " +
                " 	FROM ( " +
                " 		SELECT " +
                " 			max(a.`报价编号`) AS 报价编号 " +
                " 		FROM 报价单表 a " +
                " 		WHERE a.许可 = :permit " +
                " 		AND   a.金融 IS NOT NULL " +
                " 		GROUP BY a.`初始报价单号` " +
                " 	) a " +
                " 	LEFT JOIN 报价单表 b  on a.报价编号 = b.报价编号 " +
                " ) a " +
                " left join lsh库存表 i on i.库存编号 = a.库存编号 " +
                " left join 用户 k on k.用户名 = a.`销售顾问` " +
                " left JOIN `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号 " +
                " left join 报价金融表 c on a.金融 = c.金融编号 " +
                " left join `lsh经销商表` e on e.许可_bmbs = a.许可 " +
                " left join lsh车型 f on f.车型编号 = a.车型编号 " +
                " left join Lsh金融申请 h  on h.初始报价单号 = a.初始报价单号 " +
                " LEFT JOIN car_sales_state m ON b.`新车销售状态` = m.`key` " +
                " LEFT JOIN `报价交车表` x ON a.`交车` = x.`编号` " +
                " where c.金融机构='奔驰租赁' " +
                " and h.状态 not in ('00','09','07','08','11','12','13','14') " +
                " and (h.`撤单原因` is null and h.`撤单日期` is null) ");
        if (re.name != null && !"".equals(re.name)) {
            sql.append("  and  b.`顾客姓名` like  :names  ");
            params.put("names", "%" + re.name + "%");
        }
        if (re.nameTel != null && !"".equals(re.nameTel)) {
            sql.append("  and  b.电话号码 like  :tels  ");
            params.put("tels", "%" + re.nameTel + "%");
        }
        if (re.moneyorgina != null && !"".equals(re.moneyorgina)) {
            sql.append("  and  c.金融机构 like  :orgs  ");
            params.put("orgs", "%" + re.moneyorgina + "%");
        }
        if (re.solestatus != null && !"".equals(re.solestatus) && !re.solestatus.equals("0")) {
            if (re.solestatus.equals("1")) {  /*00金融编辑中		=SC未提交计算申请
                01金融未报价		=待报价
                02金融报价完成		=报价完成
                03金融准战败		=战败
                04金融战败（app不可编辑，等待PC激活）
                05金融意向确认
                06金融已申请待批复
                07金融已申请批复通过
                08金融已申请批复未通过
                09金融通过后撤单
                10金融抵押完成
                11金融放款到帐
                12新车战败*/

                sql.append("  and  h.`状态`='01'  ");
            } else if (re.solestatus.equals("2")) {

                sql.append("  and h.`状态`='02' ");
            } else if (re.solestatus.equals("3")) {

                sql.append("  and h.`状态`='03' ");
            } else if (re.solestatus.equals("4")) {

                sql.append("  and h.`状态`='05' ");
            } else if (re.solestatus.equals("5")) {

                sql.append("  and h.`状态`='04' ");


            } else if (re.solestatus.equals("6")) {

                sql.append("  and h.`状态`='06' ");
            } else if (re.solestatus.equals("7")) {

                sql.append("  and h.`状态`='07' ");
            } else if (re.solestatus.equals("8")) {

                sql.append("  and h.`状态`='08' ");
            } else if (re.solestatus.equals("9")) {

                sql.append("  and h.`状态`='10' ");
            } else {
                sql.append("  and 1=1 ");
            }

        }
        sql.append("  ORDER BY h.`更新时间` desc   ");

        return this.getPage(sql.toString(), params, pa, permit);
    }


    // 拿出 所有 的 普通的
    public PageData getFinancialCommonDetailListByUsername(String permits, String username, FinanceService.getfinancialFailAllListitemtwo re, int permit, int pa) {
        Map params = Maps.newHashMap();
        params.put("permit", permits);
        StringBuffer sql = new StringBuffer();
        sql.append("select   \n" +
                "  f.车型编号,   \n" +
                "  b.`顾客姓名` as 客户姓名,   \n" +
                "  b.电话号码 as  客户电话,   \n" +
                "  CASE  \n" +
                "    WHEN NOT ISNULL(m.`交车日期`) THEN '交车完毕'  \n" +
                "    WHEN (NOT ISNULL(m.`开票时间`) AND ISNULL(m.`退票时间`)) OR (NOT ISNULL(m.`开票时间`) AND NOT ISNULL(m.`退票时间`) AND m.`开票时间` > m.`退票时间`) THEN '零售'  \n" +
                "    WHEN NOT ISNULL(m.`退票时间`) AND qo.`订金类型`='订金' AND ISNULL(qo.`订单失销日期`) THEN '订金'  \n" +
                "    WHEN NOT ISNULL(qs.`应付金额`) AND NOT ISNULL(qs.`实付金额`) AND ROUND(qs.`实付金额`, 4) >= ROUND(qs.`应付金额`, 4) THEN '全款到账'  \n" +
                "    WHEN NOT ISNULL(m.`合同签约日期`) THEN '合同'  \n" +
                "    WHEN NOT ISNULL(qo.`订单审批通过日期`) AND ISNULL(qo.`订单失销日期`) AND qo.`订金类型`='订金' THEN '订金'  \n" +
                "    WHEN NOT ISNULL(qo.`订单审批通过日期`) AND ISNULL(qo.`订单失销日期`) AND qo.`订金类型`='意向金' THEN '意向金'  \n" +
                "    ELSE '报价'  \n" +
                "  END AS 新车销售状态,  \n" +
                "  c.贷款额 as 贷款金额,   \n" +
                "  i.车架号,   \n" +
                "  i.颜色,   \n" +
                "  IF(h.更新账号 is null, '', (select i.人员姓名 from 用户 i where i.用户名 =h.更新账号)) as 所属金融专员,   \n" +
                "  if(b.顾客性别='男','先生','女士') as 称谓,   \n" +
                "  c.贷款类型,   \n" +
                "  a.折后价 车辆销售价,   \n" +
                "  e.经销商名 零售经销商,   \n" +
                "  f.`车型` 意向车型,   \n" +
                "  f.`车型详细` 意向车款,   \n" +
                "  k.`人员姓名` as 销售顾问,   \n" +
                "  c.热销级别,   \n" +
                "  c.按揭类型,   \n" +
                "  c.金融机构,   \n" +
                "  c.贷款期数 as 期数,   \n" +
                "  c.`保值金额`,   \n" +
                "  if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null ,0, c.金融利率),0) as 利率,   \n" +
                "  if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null ,0, c.金融利率),0) as 费率,   \n" +
                "  c.保值比例,   \n" +
                "  c.首付比例 as 首付比例,   \n" +
                "  c.`首付额` as 首付金额,   \n" +
                "  c.保证金比例,   \n" +
                "  c.`尾款比例`,   \n" +
                "  c.`尾款额` as 尾款金额,   \n" +
                "  c.`金融服务费`,   \n" +
                "  c.`金融手续费`,   \n" +
                "  c.保证金金额,   \n" +
                "  c.月付额  as 月供,   \n" +
                "  h.初始报价单号,   \n" +
                "  a.报价编号,   \n" +
                "  c.金融编号,   \n" +
                "  c.金融产品编码,   \n" +
                "  IF(m.`开票时间` IS NULL, '', DATE_FORMAT(m.`开票时间`, '%Y-%m-%d')) 开票日期,   \n" +
                "  IF(m.`退票时间` IS NULL, '', DATE_FORMAT(m.`退票时间`, '%Y-%m-%d')) 退票日期,   \n" +
                "  h.`操作人`,   \n" +
                "  h.`更新时间`,   \n" +
                "  '' as 备注,   \n" +
                "  CASE                                                \n" +
                "    WHEN h.`状态`='01' THEN '汽车金融待报价'            \n" +
                "    WHEN h.`状态`='02' THEN '汽车金融报价完成'          \n" +
                "    WHEN h.`状态`='03' THEN '准全款潜客'            \n" +
                "    WHEN h.`状态`='04' THEN '汽车金融战败'          \n" +
                "    WHEN h.`状态`='05' THEN '汽车金融意向确认'          \n" +
                "    WHEN h.`状态`='06' THEN '已申请-待批复'          \n" +
                "    WHEN h.`状态`='07' THEN '已申请-批复通过'            \n" +
                "    WHEN h.`状态`='08' THEN '已申请-批复未通过'            \n" +
                "    WHEN h.`状态`='09' THEN '通过后撤单'            \n" +
                "    WHEN h.`状态`='10' THEN '抵押完成'            \n" +
                "    WHEN h.`状态`='12' THEN '新车战败'           \n" +
                "    WHEN h.`状态`='11' THEN '放款到账'           \n" +
                "    WHEN h.`状态`='13' THEN '撤单后重新申请'        \n" +
                "    WHEN h.`状态`='14' THEN '条件批复'              \n" +
                "    ELSE '金融编辑中'  \n" +
                "  END as 金融销售状态 ,            \n" +
                "  CASE          \n" +
                "    WHEN h.`状态`='06' THEN '待批复'         \n" +
                "    WHEN h.`状态`='07' THEN '批复通过'            \n" +
                "    WHEN h.`状态`='08' THEN '批复未通过'           \n" +
                "    WHEN h.`状态`='10' THEN '抵押完成'            \n" +
                "    WHEN h.`状态`='12' THEN '新车战败'              \n" +
                "    WHEN h.`状态`='13' THEN '撤单后重新申请'        \n" +
                "    WHEN h.`状态`='14' THEN '条件批复'                              \n" +
                "    ELSE '汽车金融待报价'  \n" +
                "  END AS 审批进度     \n" +
                "from (   \n" +
                "  SELECT    \n" +
                "    b.报价编号,   \n" +
                "    b.`初始报价单号`,   \n" +
                "    b.`顾客编号`,   \n" +
                "    b.折后价,   \n" +
                "    b.库存编号,   \n" +
                "    b.金融,   \n" +
                "    b.许可,   \n" +
                "    b.`销售顾问`,   \n" +
                "    b.`交车`,  \n" +
                "    b.车型编号 ,  \n" +
                "    b.订单编号  \n" +
                "  FROM (   \n" +
                "    SELECT max(a.`报价编号`) AS 报价编号   \n" +
                "    FROM 报价单表 a   \n" +
                "    WHERE a.许可 = :permit  \n" +
                "    AND   a.金融 IS NOT NULL   \n" +
                "    GROUP BY a.`初始报价单号`   \n" +
                "  ) a   \n" +
                "  LEFT JOIN 报价单表 b on a.报价编号 = b.报价编号   \n" +
                ") a   \n" +
                "LEFT JOIN lsh库存表 i on i.库存编号 = a.库存编号   \n" +
                "LEFT JOIN `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号   \n" +
                "LEFT JOIN 报价金融表 c on a.金融 = c.金融编号   \n" +
                "LEFT JOIN 用户 k on k.用户名 = a.`销售顾问`   \n" +
                "LEFT JOIN `lsh经销商表` e on e.许可_bmbs = a.许可   \n" +
                "LEFT JOIN lsh车型 f on f.车型编号 = a.车型编号   \n" +
                "LEFT JOIN Lsh金融申请 h  on h.初始报价单号 = a.初始报价单号   \n" +
                "left join 报价订单表 qo on a.订单编号 = qo.订单编号  \n" +
                "left join 报价付款详细 qs on a.报价编号 = qs.报价编号 and 付款事由 = '总计'  \n" +
                "LEFT JOIN `报价交车表` m ON a.`交车` = m.`编号`   \n" +
                "where (c.金融机构<>'奔驰租赁' or c.金融机构 is null)   \n" +
                "and not exists (select `金融编号` from `报价金融详细表` where `金融编号`=c.`金融编号` ) "  +
//                "and c.`金融编号` not in(select DISTINCT 金融编号 from 报价金融详细表)    \n" +
                "and h.状态 not in ('00','09','11','07','08','12','13','14')   \n" +
                "and (h.`撤单原因` is null and h.`撤单日期` is null)   \n");

        if (re.name != null && !"".equals(re.name)) {
            sql.append("  and  b.`顾客姓名` like  :names  ");
            params.put("names", "%" + re.name + "%");
        }
        if (re.nameTel != null && !"".equals(re.nameTel)) {
            sql.append("  and  b.电话号码 like  :tels  ");
            params.put("tels", "%" + re.nameTel + "%");
        }
        if (re.moneyorgina != null && !"".equals(re.moneyorgina)) {
            sql.append("  and  c.金融机构 like  :orgs  ");
            params.put("orgs", "%" + re.moneyorgina + "%");
        }
        if (re.solestatus != null && !"".equals(re.solestatus) && !re.solestatus.equals("0")) {
            if (re.solestatus.equals("1")) {  /*00金融编辑中		=SC未提交计算申请
                01金融未报价		=待报价
                02金融报价完成		=报价完成
                03金融准战败		=战败
                04金融战败（app不可编辑，等待PC激活）
                05金融意向确认
                06金融已申请待批复
                07金融已申请批复通过
                08金融已申请批复未通过
                09金融通过后撤单
                10金融抵押完成
                11金融放款到帐
                12新车战败*/

                sql.append("  and  h.`状态`='01'  ");
            } else if (re.solestatus.equals("2")) {

                sql.append("  and h.`状态`='02' ");
            } else if (re.solestatus.equals("3")) {

                sql.append("  and h.`状态`='03' ");
            } else if (re.solestatus.equals("4")) {

                sql.append("  and h.`状态`='05' ");
            } else if (re.solestatus.equals("5")) {

                sql.append("  and h.`状态`='04' ");


            } else if (re.solestatus.equals("6")) {

                sql.append("  and h.`状态`='06' ");
            } else if (re.solestatus.equals("7")) {

                sql.append("  and h.`状态`='07' ");
            } else if (re.solestatus.equals("8")) {

                sql.append("  and h.`状态`='08' ");
            } else if (re.solestatus.equals("9")) {

                sql.append("  and h.`状态`='10' ");
            } else {
                sql.append("  and 1=1 ");
            }

        }

        if ( re.更新时间起始 != null) {
            sql.append("  and  h.更新时间 > :更新时间起始 ");
            params.put("更新时间起始", re.更新时间起始);
        }
        if ( re.更新时间结束 != null) {
            sql.append("  and  h.更新时间 < :更新时间结束  ");
            params.put("更新时间结束", DateUtils.addDate(re.更新时间结束,Calendar.DAY_OF_MONTH, 1));
        }
        sql.append("  ORDER BY h.`更新时间` desc   ");

        return this.getPage(sql.toString(), params, pa, permit);
    }

    public List getFinaceCarprice(String permits, String re) {
        Map params = Maps.newHashMap();
        params.put("permit", permits);
        params.put("code", re);// 当顾客 不选 金融时， 金融机构肯定是空的
        StringBuffer sql = new StringBuffer();
        sql.append("select    a.折后价 as 车价 ,   h.初始报价单号    \n" +
                "                 from       \n" +
                "                报价单表 a    \n" +
                "                join `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号   \n" +
                "                join 报价金融表 c on a.金融=c.金融编号   \n" +
                "                join `lsh经销商表` e on e.许可_bmbs=a.许可   \n" +
                "                join lsh车型 f on f.车型编号=a.车型编号   \n" +
                "                join   \n" +
                "                ( SELECT a.金融, a.`初始报价单号`, y.报价编号 FROM 报价单表 a JOIN ( SELECT max(a.`报价编号`)  \n" +
                "                             AS 报价编号 FROM 报价单表 a WHERE a.许可 =:permit   \n" +
                "                                 GROUP BY a.`初始报价单号` ) y ON a.报价编号 = y.报价编号 WHERE a.金融 IS NOT NULL   ) g    \n" +
                "                on g.金融=c.金融编号       \n" +
                "                join lsh金融申请 h  on h.初始报价单号=g.初始报价单号   \n" +
                "                where (c.金融机构<>'奔驰租赁' or c.金融机构 is null  ) \n" +
                "                and  c.`金融编号` not  in(      \n" +
                "                select DISTINCT 金融编号 from 报价金融详细表  ) " +
                "   and 1=1   and h.`初始报价单号`=:code  and (h.`撤单原因` is null and h.`撤单日期` is null )     ");

        sql.append("  ORDER BY h.`更新时间` desc   ");

        return this.getList(sql.toString(), params);
    }

    @Override
    public List getFinancialCommonDetailSingle(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sql = new StringBuffer();
        sql.append(" select  f.车型编号,  b.`顾客姓名` as 客户姓名 ,b.电话号码 as  客户电话, c.贷款额 as 贷款金额, c.利率状态 ,   \n" +
                "i.车架号 as VIN ,i.颜色," +
                " f.车型详细 as  意向车款, " +
                "   IF(h.更新账号 is null, '', (select i.人员姓名 from 用户 i where i.用户名 = h.更新账号)) as 所属金融专员, " +
                "if(b.顾客性别='男','先生','女士') as 称谓,   c.贷款类型 ,       \n" +
                "CASE                                             \n" +
                "WHEN b.新车销售状态 ='1' THEN '待甄别'          \n" +
                "WHEN b.新车销售状态 ='2' THEN '待邀约'        \n" +
                "WHEN b.新车销售状态 ='3' THEN '待分配'          \n" +
                "WHEN b.新车销售状态 ='4' THEN '分配中'        \n" +
                "WHEN b.新车销售状态 ='5' THEN '分配不成功'        \n" +
                "WHEN b.新车销售状态 ='6' THEN '分配完成'        \n" +
                "WHEN b.新车销售状态 ='7' THEN '初次联系完成'          \n" +
                "WHEN b.新车销售状态 ='8' THEN '新车战败'          \n" +
                "WHEN b.新车销售状态 ='9' THEN '到店接待'          \n" +
                "WHEN b.新车销售状态 ='10' THEN '报价'          \n" +
                "WHEN b.新车销售状态 ='11' THEN '待跟进'         \n" +
                "WHEN b.新车销售状态 ='12' THEN '意向金'         \n" +
                "WHEN b.新车销售状态  ='13' THEN '订金'         \n" +
                "WHEN b.新车销售状态  ='14' THEN '首付款到账'     \n" +
                "WHEN b.新车销售状态 ='15' THEN '全款到账'          \n" +
                "WHEN b.新车销售状态  ='16' THEN '合同'         \n" +
                "WHEN b.新车销售状态  ='17' THEN '零售'          \n" +
                "WHEN b.新车销售状态  ='18' THEN '交车完毕'            \n" +
                "ELSE '汽车销售中' END       \n" +
                "as  新车销售状态,        \n" +
                "a.折后价  车价 ,e.经销商名 零售经销商 ,f.`车型详细_CN` 意向车型,        \n" +
                "k.`人员姓名` as 销售顾问,   c.热销级别, c.按揭类型, c.金融机构,        \n" +
                "c.贷款期数 as 期数,    \n" +
                " if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null,'',c.金融利率),'') as 利率,   " +
                " if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null,'',c.金融利率),'') as 费率,                 \n" +
                "ifnull(c.保值比例,0)   保值比例 ,   c.`保值金额`, c.首付比例 as 首付比例,c.`首付额` as 首付金额,         \n" +
                "ifnull(c.保证金比例,0)   as 保证比例,    c.`尾款比例`,  c.`尾款额` as 尾款金额,  c.`金融服务费`,       \n" +
                " c.`金融手续费`,  "+
                "c.保证金金额 as 保证金额,        \n" +
                "c.月付额  as 月供,   h.初始报价单号,  a.报价编号, c.金融编号, c.金融产品编码,  '' as 备注,       \n" +
                "CASE                                             \n" +
                "WHEN h.`状态`='01' THEN '汽车金融待报价'         \n" +
                "WHEN h.`状态`='02' THEN '汽车金融报价完成'       \n" +
                "WHEN h.`状态`='03' THEN '准全款潜客'         \n" +
                "WHEN h.`状态`='04' THEN '汽车金融战败'       \n" +
                "WHEN h.`状态`='05' THEN '汽车金融意向确认'       \n" +
                "WHEN h.`状态`='06' THEN '已申请-待批复'       \n" +
                "WHEN h.`状态`='07' THEN '已申请-批复通过'         \n" +
                "WHEN h.`状态`='08' THEN '已申请-批复未通过'         \n" +
                "WHEN h.`状态`='09' THEN '通过后撤单'         \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'         \n" +
                "WHEN h.`状态`='12' THEN '新车战败'        \n" +
                "WHEN h.`状态`='11' THEN '放款到账'        \n" +
                "WHEN h.`状态`='13' THEN '撤单后重新申请'     \n" +
                "WHEN h.`状态`='14' THEN '条件批复'                                        \n" +
                "ELSE '汽车金融待报价' END as 金融销售状态 ,         \n" +
                "CASE       \n" +
                "WHEN h.`状态`='06' THEN '待批复'      \n" +
                "WHEN h.`状态`='07' THEN '批复通过'         \n" +
                "WHEN h.`状态`='08' THEN '批复未通过'        \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'         \n" +
                "WHEN h.`状态`='12' THEN '新车战败'           \n" +
                "ELSE '金融销售中' END AS 审批进度  ,h.`操作人`,h.`更新时间`        \n" +
                "from (    \n" +
                "   SELECT     \n" +
                "       b.报价编号,b.`初始报价单号`,b.`顾客编号`,b.折后价,    \n" +
                "       b.库存编号,b.金融,b.许可,b.`销售顾问` ,b.车型编号    \n" +
                "   FROM 报价单表 b  where b.报价编号 = :quoteId     \n" +
                ") a     \n" +
                "left join lsh库存表 i on i.库存编号=a.库存编号          \n" +
                "left join 用户 k on k.用户名=a.`销售顾问`                \n" +
                "left join `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号        \n" +
                "left join 报价金融表 c on a.金融=c.金融编号        \n" +
                "left join `lsh经销商表` e on e.许可_bmbs=a.许可        \n" +
                "left join lsh车型 f on f.车型编号=a.车型编号           \n" +
                "left join lsh金融申请 h  on h.初始报价单号=a.初始报价单号      ");
        return this.getList(sql.toString(), params);
    }



    public List getRightByUserAccount(String permit, String permissioncode) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);

        params.put("code", permissioncode);
        StringBuffer sql = new StringBuffer();
        sql.append("  select    b.`顾客姓名` as 客户姓名 ,b.电话号码 as  客户电话, c.贷款额 as 贷款金额,  " +
                "        h.`操作人`,h.`更新时间`    \n" +
                "                 from       \n" +
                "                报价单表 a   " +
                "            join 用户 k on k.用户名=a.`销售顾问`           \n" +
                "                join `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号   \n" +
                "                join 报价金融表 c on a.金融=c.金融编号   \n" +
                "                join `lsh经销商表` e on e.许可_bmbs=a.许可   \n" +
                "                join lsh车型 f on f.车型编号=a.车型编号   \n" +
                "                join   \n" +
                "                ( SELECT a.金融, a.`初始报价单号`, y.报价编号 FROM 报价单表 a JOIN ( SELECT max(a.`报价编号`)  \n" +
                "                             AS 报价编号 FROM 报价单表 a WHERE a.许可 =:permit   \n" +
                "                                 GROUP BY a.`初始报价单号` ) y ON a.报价编号 = y.报价编号 WHERE a.金融 IS NOT NULL   ) g    \n" +
                "                on g.金融=c.金融编号       \n" +
                "                join lsh金融申请 h  on h.初始报价单号=g.初始报价单号   \n" +
                "                where (c.金融机构<>'奔驰租赁' or c.金融机构 is null  )   \n" +
                "                and  c.`金融编号` not  in(      \n" +
                "                select DISTINCT 金融编号 from 报价金融详细表  ) " +
                "   and 1=1  and h.状态<>'09' and h.状态<>'11' and h.状态<>'12' and h.状态<>'13'    and h.状态<>'14'  " +
                " and h.`初始报价单号`=:code  and (h.`撤单原因` is null and h.`撤单日期` is null)    ");

        sql.append("  ORDER BY h.`更新时间` desc   ");

        return this.getList(sql.toString(), params);
    }




    // 获取 某一个确定的 annualpay 的 详细信息 one
    @Override
    public List getFinancialAnnualDetailSingle(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sql = new StringBuffer();
        sql.append("  select  f.车型编号,  \n" +
                "b.`顾客姓名` AS 客户姓名,   \n" +
                "z.车架号 as VIN ,z.颜色, " +
                " f.车型详细 as  意向车款, " +
                "   IF(     h.更新账号   is null  , '',  ( select  \n" +
                "i.人员姓名  from  用户 i  where\n" +
                "i.用户名 =h.更新账号 )  )    as 所属金融专员, " +
                "GROUP_CONCAT(if(d.`期数`='' or d.`期数` is null,0,d.期数))  as 期数拆分,        \n" +
                "GROUP_CONCAT(if(d.`月供`='' or d.`月供` is null,0,d.月供))   as 月供拆分,       \n" +
                "GROUP_CONCAT(if(d.`期数编号`='' or d.`期数编号` is null,0,d.期数编号))        \n" +
                "as 期数编号拆分 ,         \n" +
                "b.电话号码 AS 客户电话,           \n" +
                "if(b.顾客性别='男','先生','女士') as 称谓,    '' as 备注 ,      \n" +
                "a.报价编号,      \n" +
                "CASE                                             \n" +
                "WHEN b.新车销售状态 ='1' THEN '待甄别'          \n" +
                "WHEN b.新车销售状态 ='2' THEN '待邀约'        \n" +
                "WHEN b.新车销售状态 ='3' THEN '待分配'          \n" +
                "WHEN b.新车销售状态 ='4' THEN '分配中'        \n" +
                "WHEN b.新车销售状态 ='5' THEN '分配不成功'        \n" +
                "WHEN b.新车销售状态 ='6' THEN '分配完成'        \n" +
                "WHEN b.新车销售状态 ='7' THEN '初次联系完成'          \n" +
                "WHEN b.新车销售状态 ='8' THEN '新车战败'          \n" +
                "WHEN b.新车销售状态 ='9' THEN '到店接待'          \n" +
                "WHEN b.新车销售状态 ='10' THEN '报价'          \n" +
                "WHEN b.新车销售状态 ='11' THEN '待跟进'         \n" +
                "WHEN b.新车销售状态 ='12' THEN '意向金'         \n" +
                "WHEN b.新车销售状态  ='13' THEN '订金'         \n" +
                "WHEN b.新车销售状态  ='14' THEN '首付款到账'     \n" +
                "WHEN b.新车销售状态 ='15' THEN '全款到账'          \n" +
                "WHEN b.新车销售状态  ='16' THEN '合同'         \n" +
                "WHEN b.新车销售状态  ='17' THEN '零售'          \n" +
                "WHEN b.新车销售状态  ='18' THEN '交车完毕'            \n" +
                "ELSE '汽车销售中' END       \n" +
                "as  新车销售状态,        \n" +
                "a.折后价 车价,e.经销商名 零售经销商,f.`车型详细_CN` 意向车型,         \n" +
                "k.`人员姓名` as 销售顾问, c.热销级别, c.按揭类型,  c.金融机构,            \n" +
                "if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null ,'',c.金融利率),'') as 利率,   " +
                "if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null ,'',c.金融利率),'')as 费率,                 \n" +
                "c.`贷款类型`,  c.贷款期数  as 期数 ,c.贷款额  贷款金额,     \n" +
                "ifnull(c.保证金比例,0) as   保证比例,          \n" +
                "c.保证金金额 保证金额,c.首付比例,c.首付额 首付金额,             \n" +
                "c.尾款比例,     c.尾款额 尾款金额,c.金融服务费,                    \n" +
                " c.`金融手续费`, c.金融产品编码,  "+
                "c.金融编号,  h.初始报价单号,      \n" +
                "CASE                                             \n" +
                "WHEN h.`状态`='01' THEN '汽车金融待报价'         \n" +
                "WHEN h.`状态`='02' THEN '汽车金融报价完成'       \n" +
                "WHEN h.`状态`='03' THEN '准全款潜客'         \n" +
                "WHEN h.`状态`='04' THEN '汽车金融战败'       \n" +
                "WHEN h.`状态`='05' THEN '汽车金融意向确认'       \n" +
                "WHEN h.`状态`='06' THEN '已申请-待批复'       \n" +
                "WHEN h.`状态`='07' THEN '已申请-批复通过'         \n" +
                "WHEN h.`状态`='08' THEN '已申请-批复未通过'         \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'         \n" +
                "ELSE '汽车金融待报价' END as 金融销售状态 ,         \n" +
                "CASE       \n" +
                "WHEN h.`状态`='06' THEN '待批复'      \n" +
                "WHEN h.`状态`='07' THEN '批复通过'         \n" +
                "WHEN h.`状态`='08' THEN '批复未通过'        \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'         \n" +
                "WHEN h.`状态`='12' THEN '新车战败'           \n" +
                "ELSE '金融销售中' END AS 审批进度 ,''还款结束期,'' 还款额    \n" +
                "from (\n" +
                "SELECT \n" +
                "b.报价编号,b.`初始报价单号`,b.`顾客编号`,b.折后价,\n" +
                "b.库存编号,b.金融,b.许可,b.`销售顾问` ,b.车型编号\n" +
                "FROM 报价单表 b  where b.报价编号 = :quoteId \n" +
                ") a \n" +
                "left join   lsh库存表 z on z.库存编号=a.库存编号     \n" +
                "left join 用户 k on k.用户名=a.`销售顾问`     \n" +
                "left join `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号        \n" +
                "left join 报价金融表 c on a.金融=c.金融编号       \n" +
                "left join   `报价金融详细表`    d on  d.金融编号=c.金融编号  \n" +
                "left join `lsh经销商表` e on e.许可_bmbs=a.许可       \n" +
                "left join lsh车型 f on f.车型编号=a.车型编号         \n" +
                " left join lsh金融申请 h  on h.初始报价单号=a.初始报价单号       \n");


        return this.getList(sql.toString(), params);
    }

    @Override
    public List getFinancialAnnualDetailSinglebyPermitAndInitial(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT a.* " +
                " FROM 报价金融详细表 a " +
                " JOIN 报价单表 b ON a.`金融编号` = b.`金融` " +
                " where b.`报价编号` = :quoteId ");

        return this.getList(sql.toString(), params);
    }

    @Override
    public List getjordanonetwo(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT  f.车型编号,  " +
                " b.`顾客姓名` AS 客户姓名,    " +
                " b.电话号码 AS 客户电话,    " +
                "IF (    " +
                " b.顾客性别 = '男',    " +
                " '先生',    " +
                " '女士'    " +
                ") AS 称谓,    " +
                " ''  as 备注,    " +
                " a.报价编号,    " +
                "  CASE                                         \n" +
                "WHEN b.新车销售状态 ='1' THEN '待甄别'      \n" +
                "WHEN b.新车销售状态 ='2' THEN '待邀约'    \n" +
                "WHEN b.新车销售状态 ='3' THEN '待分配'      \n" +
                "WHEN b.新车销售状态 ='4' THEN '分配中'    \n" +
                "WHEN b.新车销售状态 ='5' THEN '分配不成功'    \n" +
                "WHEN b.新车销售状态 ='6' THEN '分配完成'    \n" +
                "WHEN b.新车销售状态 ='7' THEN '初次联系完成'      \n" +
                "WHEN b.新车销售状态 ='8' THEN '新车战败'      \n" +
                "WHEN b.新车销售状态 ='9' THEN '到店接待'      \n" +
                "WHEN b.新车销售状态 ='10' THEN '报价'      \n" +
                "WHEN b.新车销售状态 ='11' THEN '待跟进'     \n" +
                "WHEN b.新车销售状态 ='12' THEN '意向金'     \n" +
                "WHEN b.新车销售状态  ='13' THEN '订金'     \n" +
                "WHEN b.新车销售状态  ='14' THEN '首付款到账' \n" +
                "WHEN b.新车销售状态 ='15' THEN '全款到账'      \n" +
                "WHEN b.新车销售状态  ='16' THEN '合同'     \n" +
                "WHEN b.新车销售状态  ='17' THEN '零售'      \n" +
                "WHEN b.新车销售状态  ='18' THEN '交车完毕'        \n" +
                "ELSE '汽车销售中' END   \n" +
                "as  新车销售状态,     " +
                " a.折后价 车价,    " +
                " e.经销商名 零售经销商,    " +
                " f.`车型详细_CN` 意向车型,    " +
                " f.车型详细 as  意向车款, " +
                " k.`人员姓名` as 销售顾问,   " +
                " c.热销级别,    " +
                " c.按揭类型,    " +
                " c.金融机构 as 金机,    " +
                "            if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null ,0,  c.金融利率   ),0) as 利率,   " +
                "  if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null ,0, c.金融利率),0)as 费率,                 \n" +
                " c.`贷款类型`,    " +
                " c.贷款期数 AS 期数,    " +
                " c.贷款额 贷款金额,    " +
                " c.保证金比例 保证比例,    " +
                " c.保证金金额 保证金额,    " +
                " c.首付比例,    " +
                " c.首付额 首付金额,    " +
                " c.尾款比例,    " +
                " c.尾款额 尾款金额,    " +
                " c.金融服务费,    " +
                " c.`金融手续费`,  "+
                " c.金融编号,    " +
                " h.初始报价单号,    " +
                "          CASE                                         \n" +
                "                 WHEN h.`状态`='01' THEN '汽车金融待报价'     \n" +
                "                 WHEN h.`状态`='02' THEN '汽车金融报价完成'   \n" +
                "                 WHEN h.`状态`='03' THEN '准全款潜客'     \n" +
                "                 WHEN h.`状态`='04' THEN '汽车金融战败'   \n" +
                "                 WHEN h.`状态`='05' THEN '汽车金融意向确认'   \n" +
                "                                      WHEN h.`状态`='06' THEN '已申请-待批复'   \n" +
                "                                          WHEN h.`状态`='07' THEN '已申请-批复通过'     \n" +
                "                                        WHEN h.`状态`='08' THEN '已申请-批复未通过'     \n" +
                "                                        WHEN h.`状态`='09' THEN '通过后撤单'     \n" +
                "                              WHEN h.`状态`='10' THEN '抵押完成'     \n" +
                "                                WHEN h.`状态`='12' THEN '新车战败'    \n" +
                "                                WHEN h.`状态`='11' THEN '放款到账'    \n" +
                "                                WHEN h.`状态`='13' THEN '撤单后重新申请'    \n" +
                "                                 ELSE '汽车金融待报价' END as 金融销售状态 ,     \n" +
                "                                 CASE   \n" +
                "                                          WHEN h.`状态`='06' THEN '待批复'  \n" +
                "                                           WHEN h.`状态`='07' THEN '批复通过'     \n" +
                "                                          WHEN h.`状态`='08' THEN '批复未通过'    \n" +
                "                                  WHEN h.`状态`='10' THEN '抵押完成'     \n" +
                "                                 WHEN h.`状态`='12' THEN '新车战败'       \n" +
                "   ELSE '金融销售中' END AS 审批进度,    " +
                " '' 还款结束期,    " +
                " '' 还款额    " +
                "FROM 报价单表 a  " +
                " left  join 用户 k on k.用户名=a.`销售顾问`     " +
                " left  JOIN `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号    " +
                " left  JOIN 报价金融表 c ON a.金融 = c.金融编号    " +
                " left  JOIN `lsh经销商表` e ON e.许可_bmbs = a.许可    " +
                " left  JOIN lsh车型 f ON f.车型编号 = a.车型编号    " +
                " left   JOIN lsh金融申请 h ON h.初始报价单号 = a.初始报价单号    " +
                "WHERE a.报价编号 = :quoteId and h.状态<>'09' and h.状态<>'11' and h.状态<>'12' and h.状态<>'13'   and h.状态<>'14'  " +
                " and (h.`撤单原因` is null and h.`撤单日期` is null)    ");

        return this.getList(sql.toString(), params);
    }
    // james 奔驰租赁
    @Override
    public List getBenzDetailListByPermit(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sql = new StringBuffer();
        sql.append("select f.车型编号, \n" +
                "b.`顾客姓名` as 客户姓名 ,b.电话号码 as  客户电话,  \n" +
                "i.车架号 as VIN ,i.颜色," +
                " f.车型详细 as  意向车款, " +
                "   IF(     h.更新账号   is null  , '',  ( select  \n" +
                "i.人员姓名  from  用户 i  where\n" +
                "i.用户名 =h.更新账号 )  )    as 所属金融专员, " +
                "if(b.顾客性别='男','先生','女士') as 称谓,   \n" +
                "CASE                                          \n" +
                "WHEN b.新车销售状态 ='1' THEN '待甄别'       \n" +
                "WHEN b.新车销售状态 ='2' THEN '待邀约'     \n" +
                "WHEN b.新车销售状态 ='3' THEN '待分配'       \n" +
                "WHEN b.新车销售状态 ='4' THEN '分配中'     \n" +
                "WHEN b.新车销售状态 ='5' THEN '分配不成功'     \n" +
                "WHEN b.新车销售状态 ='6' THEN '分配完成'     \n" +
                "WHEN b.新车销售状态 ='7' THEN '初次联系完成'       \n" +
                "WHEN b.新车销售状态 ='8' THEN '新车战败'       \n" +
                "WHEN b.新车销售状态 ='9' THEN '到店接待'       \n" +
                "WHEN b.新车销售状态 ='10' THEN '报价'       \n" +
                "WHEN b.新车销售状态 ='11' THEN '待跟进'      \n" +
                "WHEN b.新车销售状态 ='12' THEN '意向金'      \n" +
                "WHEN b.新车销售状态  ='13' THEN '订金'      \n" +
                "WHEN b.新车销售状态  ='14' THEN '首付款到账'  \n" +
                "WHEN b.新车销售状态 ='15' THEN '全款到账'       \n" +
                "WHEN b.新车销售状态  ='16' THEN '合同'      \n" +
                "WHEN b.新车销售状态  ='17' THEN '零售'       \n" +
                "WHEN b.新车销售状态  ='18' THEN '交车完毕'         \n" +
                "ELSE '汽车销售中' END    \n" +
                "as  新车销售状态,      \n" +
                "a.折后价 车价,e.经销商名 零售经销商,f.`车型详细_CN` 意向车型,    \n" +
                "k.`人员姓名` as 销售顾问, c.热销级别, c.按揭类型, c.金融机构,           \n" +
                "            if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null,0,  c.金融利率   ),0) as 利率,   " +
                "  if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null,0, c.金融利率),0)as 费率,                 \n" +
                "if(c.`产品类型`='0','普通','星时享')  产品类型,  c.贷款期数 as 合同期（月数）,  \n" +
                "c.保值参数 as 年公里数,        \n" +
                "c.保值比例,c.`保值金额`,c.`修养套餐金额`,c.首付比例 as 预付比例,c.`首付额` as 预付金额,     \n" +
                "IFNULL(c.保证金比例,0) AS 保证金比例,     \n" +
                "c.保证金金额,    \n" +
                "c.金融服务费,   c.贷款额 as 贷款金额  ,c.月付额 as 月供, c.金融产品编码 ,''  as 备注,     \n" +
                " c.`金融手续费`,  "+
                "CASE                                          \n" +
                "WHEN h.`状态`='01' THEN '汽车金融待报价'      \n" +
                "WHEN h.`状态`='02' THEN '汽车金融报价完成'    \n" +
                "WHEN h.`状态`='03' THEN '准全款潜客'      \n" +
                "WHEN h.`状态`='04' THEN '汽车金融战败'    \n" +
                "WHEN h.`状态`='05' THEN '汽车金融意向确认'    \n" +
                "WHEN h.`状态`='06' THEN '已申请-待批复'    \n" +
                "WHEN h.`状态`='07' THEN '已申请-批复通过'      \n" +
                "WHEN h.`状态`='08' THEN '已申请-批复未通过'      \n" +
                "WHEN h.`状态`='09' THEN '通过后撤单'      \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'      \n" +
                "WHEN h.`状态`='12' THEN '新车战败'     \n" +
                "WHEN h.`状态`='11' THEN '放款到账'     \n" +
                "WHEN h.`状态`='13' THEN '撤单后重新申请'     \n" +
                "ELSE '汽车金融待报价' END as 金融销售状态 ,      \n" +
                "CASE    \n" +
                "WHEN h.`状态`='06' THEN '待批复'   \n" +
                " WHEN h.`状态`='07' THEN '批复通过'      \n" +
                "WHEN h.`状态`='08' THEN '批复未通过'     \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'      \n" +
                "WHEN h.`状态`='12' THEN '新车战败'        \n" +
                "ELSE '金融销售中' END AS 审批进度  , h.初始报价单号,  a.报价编号, c.金融编号,  \n" +
                " h.`操作人`,h.`更新时间`   " +
                " from   " +
                "  (\n" +
                "SELECT \n" +
                "b.报价编号,b.`初始报价单号`,b.`顾客编号`,b.折后价,\n" +
                "b.库存编号,b.金融,b.许可,b.`销售顾问` ,b.车型编号\n" +
                "FROM 报价单表 b  where b.报价编号 = :quoteId \n" +
                ") a  \n" +
                "left  join   lsh库存表 i on i.库存编号=a.库存编号     \n" +
                "left join 用户 k on k.用户名=a.`销售顾问`     \n" +
                "left JOIN `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号    \n" +
                "left join 报价金融表 c on a.金融=c.金融编号   \n" +
                "left join `lsh经销商表` e on e.许可_bmbs=a.许可   \n" +
                "left join lsh车型 f on f.车型编号=a.车型编号   \n" +
                "left join lsh金融申请 h  on h.初始报价单号=a.初始报价单号    ");


        return this.getList(sql.toString(), params);
    }

    @Override
    public List getjamesonethreeandkobeone(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sql = new StringBuffer();
        sql.append("  select f.车型编号, b.`顾客姓名` as 客户姓名 ,b.电话号码 as  客户电话, " +
                " f.车型详细 as  意向车款, " +
                "   IF(     h.更新账号   is null  , '',  ( select  \n" +
                "i.人员姓名  from  用户 i  where\n" +
                "i.用户名 =h.更新账号 )  )    as 所属金融专员, " +
                "if(b.顾客性别='男','先生','女士') as 称谓,    \n" +
                "CASE                                          \n" +
                "WHEN b.新车销售状态 ='1' THEN '待甄别'       \n" +
                "WHEN b.新车销售状态 ='2' THEN '待邀约'     \n" +
                "WHEN b.新车销售状态 ='3' THEN '待分配'       \n" +
                "WHEN b.新车销售状态 ='4' THEN '分配中'     \n" +
                "WHEN b.新车销售状态 ='5' THEN '分配不成功'     \n" +
                "WHEN b.新车销售状态 ='6' THEN '分配完成'     \n" +
                "WHEN b.新车销售状态 ='7' THEN '初次联系完成'       \n" +
                "WHEN b.新车销售状态 ='8' THEN '新车战败'       \n" +
                "WHEN b.新车销售状态 ='9' THEN '到店接待'       \n" +
                "WHEN b.新车销售状态 ='10' THEN '报价'       \n" +
                "WHEN b.新车销售状态 ='11' THEN '待跟进'      \n" +
                "WHEN b.新车销售状态 ='12' THEN '意向金'      \n" +
                "WHEN b.新车销售状态  ='13' THEN '订金'      \n" +
                "WHEN b.新车销售状态  ='14' THEN '首付款到账'  \n" +
                "WHEN b.新车销售状态 ='15' THEN '全款到账'       \n" +
                "WHEN b.新车销售状态  ='16' THEN '合同'      \n" +
                "WHEN b.新车销售状态  ='17' THEN '零售'       \n" +
                "WHEN b.新车销售状态  ='18' THEN '交车完毕'         \n" +
                "ELSE '汽车销售中' END     \n" +
                "as  新车销售状态,      \n" +
                "a.折后价 车价,e.经销商名 零售经销商,f.`车型详细_CN` 意向车型,     \n" +
                "k.`人员姓名` as 销售顾问, c.热销级别, c.按揭类型, c.金融机构 as 金机,            \n" +
                "            if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null,0,  c.金融利率   ),0) as 利率,   " +
                "  if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null,0, c.金融利率),0)as 费率,                 \n" +
                "if(c.`产品类型`='0','普通','星时享')   产品类型,  c.贷款期数 as 合同期（月数）, \n" +
                " c.保值参数 as 年公里数,         \n" +
                "c.保值比例,c.`保值金额`,c.`修养套餐金额`,c.首付比例 as 预付比例,c.`首付额` as 预付金额,      \n" +
                "c.保证金比例,      \n" +
                "c.保证金金额,     \n" +
                "c.金融服务费,   c.贷款额 as 贷款金额  ,c.月付额 as 月供, c.金融产品编码 ,     '' as 备注,      \n" +
                " c.`金融手续费`,  "+
                "CASE                                          \n" +
                "WHEN h.`状态`='01' THEN '汽车金融待报价'      \n" +
                "WHEN h.`状态`='02' THEN '汽车金融报价完成'    \n" +
                "WHEN h.`状态`='03' THEN '准全款潜客'      \n" +
                "WHEN h.`状态`='04' THEN '汽车金融战败'    \n" +
                "WHEN h.`状态`='05' THEN '汽车金融意向确认'    \n" +
                "WHEN h.`状态`='06' THEN '已申请-待批复'    \n" +
                "WHEN h.`状态`='07' THEN '已申请-批复通过'      \n" +
                "WHEN h.`状态`='08' THEN '已申请-批复未通过'      \n" +
                "WHEN h.`状态`='09' THEN '通过后撤单'      \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'      \n" +
                "WHEN h.`状态`='12' THEN '新车战败'     \n" +
                "WHEN h.`状态`='11' THEN '放款到账'     \n" +
                "WHEN h.`状态`='13' THEN '撤单后重新申请'     \n" +
                "ELSE '汽车金融待报价' END as 金融销售状态 ,      \n" +
                "CASE    \n" +
                "WHEN h.`状态`='06' THEN '待批复'   \n" +
                "WHEN h.`状态`='07' THEN '批复通过'      \n" +
                "WHEN h.`状态`='08' THEN '批复未通过'     \n" +
                "WHEN h.`状态`='10' THEN '抵押完成'      \n" +
                "WHEN h.`状态`='12' THEN '新车战败'        \n" +
                "ELSE '金融销售中' END AS 审批进度  , h.初始报价单号,  a.报价编号, c.金融编号,   \n" +
                "h.`操作人`,h.`更新时间`          \n" +
                "from     \n" +
                "  (\n" +
                "SELECT \n" +
                "b.报价编号,b.`初始报价单号`,b.`顾客编号`,b.折后价,\n" +
                "b.库存编号,b.金融,b.许可,b.`销售顾问` ,b.车型编号\n" +
                "FROM 报价单表 b  where b.报价编号 = :quoteId \n" +
                ") a \n" +
                "left join 用户 k on k.用户名=a.`销售顾问`      \n" +
                "left JOIN `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号     \n" +
                "left join 报价金融表 c on a.金融=c.金融编号    \n" +
                "left join `lsh经销商表` e on e.许可_bmbs=a.许可    \n" +
                "left join lsh车型 f on f.车型编号=a.车型编号       \n" +
                "left join lsh金融申请 h  on h.初始报价单号=a.初始报价单号    \n" +
                "      ");


        return this.getList(sql.toString(), params);
    }


    public List getSingleBenzDetail(String quoteId) {
        Map params = Maps.newHashMap();
        params.put("quoteId", quoteId);
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT a.* " +
                " FROM 报价金融表 a " +
                " JOIN 报价单表 b ON a.`金融编号` = b.金融 " +
                " where b.报价编号 = :quoteId AND a.金融机构= '奔驰租赁' ");


        return this.getList(sql.toString(), params);
    }


    public PageData getitemByfkanddetai(FinanceService.getfinancialFailAllListitemtwothreeforss re, String permit, String postman, int index, int size) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        /*params.put("postmans", postman);*/
        StringBuffer sql = new StringBuffer();
        sql.append("  select  DISTINCT b.*,\n" +
                "                CASE  \n" +
                "                 WHEN (a.`撤单日期`<>'' and a.`撤单日期` is not NULL)    \n" +
                "                 THEN '通过后撤单'    \n" +
                "                 WHEN (a.`重新申请日期`<>'' and a.`重新申请日期` is not NULL)   \n" +
                "                 THEN '撤单后重新申请'     \n" +
                "                 WHEN (a.`抵押日期`<>'' and a.`抵押日期` is not NULL and ( a.`放款日期`='' or a.`放款日期` is  NULL) )    \n" +
                "                 THEN '完成抵押'     \n" +
                "                 WHEN (a.`放款日期`<>'' and a.`放款日期` is not NULL and  a.`抵押日期`<>'' and a.`抵押日期` is not NULL )    \n" +
                "                 THEN '放款完成'    \n" +
                "                   WHEN (a.`退款金额`<>'' and a.`退款金额` is not NULL)      \n" +
                "                      THEN '退款状态' \n" +
                "          WHEN (a.`撤单日期`<>'' and a.`撤单日期` is not NULL)      \n" +
                "                      THEN '通过后退款'  " +
                "   when a.状态='14' then   '条件批复'     \n" +
                "                 ELSE '审批通过'      \n" +
                "                 END AS 审批通过后状态跟踪       \n" +
                "                 from `lsh金融申请附属` b join   \n" +
                "                ( select * from lsh金融申请 a where 1=1   and  (    a.状态='07'    " +
                "  or  a.状态='06'    or  a.状态='14'       \n" + // 意向申请 日期 有 就行
                "   or a.状态='09' or a.状态='10' or a.状态='11' or a.状态='13'    \n" +
                "                  or  a.撤单日期 is not null or a.撤单日期<>''   ) \n" +
                "or (a.`重新申请日期`<>'' and a.`重新申请日期` is not NULL   )    \n" +
                "or (a.`抵押日期`<>'' and a.`抵押日期` is not NULL   )  \n" +
                "or (a.`放款日期`<>'' and a.`放款日期` is not NULL  )  \n" +
                " or (a.`退款金额`<>'' and a.`退款金额` is not NULL  )  \n" +
                "or (a.`撤单日期`<>'' and a.`撤单日期` is not NULL)    \n" +
                "                   GROUP BY a.`初始报价单号` ORDER BY a.`更新时间` DESC \n" +
                "                ) a  on a.初始报价单号=b.初始报价单号   \n" +
                "                 join     \n" +
                "                 ( SELECT a.金融, a.`初始报价单号`, y.报价编号 FROM 报价单表 a JOIN ( SELECT max(a.`报价编号`)    \n" +
                "                    AS 报价编号 FROM 报价单表 a WHERE a.许可 =:permit     \n" +
                "                 GROUP BY a.`初始报价单号` ) y ON a.报价编号 = y.报价编号 WHERE a.金融 IS NOT NULL   ) g      \n" +
                "                  on g.初始报价单号=b.初始报价单号  where 1=1       ");
      /* public String uname;//名字                        贷款人姓名
       public String usex;//性别                          贷款人类型
       public String utel;//                               贷款人联系方式
       public String uwantcartype;//意向车型
       public String newcarsolestatus; //新车销售状态          审批后状态跟踪*/
        if (re.uname != null && !"".equals(re.uname)) {
            sql.append("  and  b.贷款人姓名 like  :names  ");
            params.put("names", "%" + re.uname + "%");
        }
        if (re.utel != null && !"".equals(re.utel)) {
            sql.append("  and  b.贷款人联系电话 like  :tels  ");
            params.put("tels", "%" + re.utel + "%");
        }
        if (re.usex != null && !"".equals(re.usex)) {
            sql.append("  and  b.贷款人类型 like  :orgs  ");
            params.put("orgs", "%" + re.usex + "%");
        } else {
            sql.append("  and 1=1 ");
        }
        if (re.newcarsolestatus != null && !"".equals(re.newcarsolestatus)) {
            if (re.newcarsolestatus.equals("通过后撤单")) {
                sql.append(" and  a.`撤单日期`<>'' and a.`撤单日期` is not NULL and a.状态='09'   ");
            } else if (re.newcarsolestatus.equals("撤单后重新申请")) {
                sql.append("  and a.`重新申请日期`<>'' and a.`重新申请日期` is not NULL and a.状态='13'    ");
            } else if (re.newcarsolestatus.equals("完成抵押")) {
                sql.append(" and  a.`抵押日期`<>'' and a.`抵押日期` is not NULL  and a.状态='10'   ");
            } else if (re.newcarsolestatus.equals("放款到账")) {
                sql.append("and  a.`放款日期`<>'' and a.`放款日期` is not NULL and a.状态='11'  ");
            } else if (re.newcarsolestatus.equals("退还服务费")) {
                sql.append("  and a.`退款金额`<>'' and a.`退款金额` is not NULL  and a.状态='04'  ");
            } else if (re.newcarsolestatus.equals("条件批复")) {
                sql.append("  and a.状态='14'  ");
            } else {
                sql.append("  and 1=1  ");
            }
        }

        sql.append(" order by a.更新时间 desc   ");
        return this.getPage(sql.toString(), params, size, index);///
    }

    public List getFailReasonList(String chushibaojai, String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer();
        sql.append("select b.战败原因,b.金融战败原因编号 FROM   \n" +
                "lsh金融战败原因附属 a    \n" +
                "join   \n" +
                "(   \n" +
                "SELECT b.`金融战败原因编号`,b.`战败原因`   \n" +
                "from   \n" +
                "lsh金融战败原因 b     \n" +
                "where b.启用状态='启用'    \n" +
                "and (b.许可='LSH' or b.许可=:permit )    \n" +
                ") b  on  a.金融战败原因编号=b.金融战败原因编号   \n" +
                "where     \n" +
                " a.战败分类='金融战败' and b.战败原因 is not null and b.`战败原因` <>'' GROUP BY b.金融战败原因编号  ");

        return this.getList(sql.toString(), params);

    }

    public List getstatusbysqls(int permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer();
        sql.append("select  a.战败原因 as d FROM   \n" +
                " `lsh金融战败原因` a    \n" +
                "where a.`金融战败原因编号`=:permit  and   \n" +
                "a.`启用状态`='启用'    ");

        return this.getList(sql.toString(), params);

    }


    public List<?> findFinanceServeFreeGuidance(String permit, int year, int month) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select :year year,a.集团指导值,a.经销商指导值,a.经销商实际值 from `lsh金融服务费` a ");
        sb.append(" where a.`许可` =:permit and YEAR(a.`日期`) =:year and MONTH(a.`日期`) =:month ");
        sb.append(" order by a.日期 ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("year", year);
        params.put("month", month);
        return this.getList(sb.toString(), params);
    }

    @Override
    public Map<String, String> getClientInfoAndMsgBypermit(String permit, String originQuotedId) {
        String sql = "  SELECT\n" +
                " a.销售顾问,\n" +
                " c.顾客姓名,\n" +
                "IF (\n" +
                " c.顾客性别 = '男',\n" +
                " '先生',\n" +
                " '女士'\n" +
                ") AS 称谓,\n" +
                " c.电话号码\n" +
                "FROM\n" +
                " 报价单表 a\n" +
                " left JOIN 顾客记录汇总 c ON a.顾客编号 = c.顾客编号\n" +
                "WHERE\n" +
                "  a.报价编号 = (\n" +
                " SELECT\n" +
                "  MAX(a.`报价编号`) AS 报价编号\n" +
                " FROM\n" +
                "  报价单表 a\n" +
                " WHERE\n" +
                "  a.许可 = :permit  \n" +
                " AND a.初始报价单号 = :originQuotedId  \n" +
                "  )    ";
        Map<String, String> params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("originQuotedId", originQuotedId);
        return (Map) getList(sql, params).get(0);
    }


    public List getFinancialdetailoneByOrgs(String name, String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT DISTINCT b.产品编码  FROM `LSH金融设置` b   \n" +
                "   LEFT JOIN `LSH先享后选设置` c USING (`金融编号`)      \n" +
                "   LEFT JOIN `LSH金融设置适用车型` d USING (`金融编号`)    \n" +
                "    LEFT JOIN `LSH金融机构设置` e USING (`金融机构编号`)   \n" +
                "    LEFT JOIN `LSH车型` z ON d.`适用车型` = z.`车型编号`    \n" +
                "   WHERE `开始日期`<=CURDATE() AND `结束日期`>=CURDATE()   " +
                "     and b.产品编码 is not null and b.产品编码 <>''      \n" +
                "             and b.`金融机构编号`  in   (   select a.金融机构编号  from           " +
                "   lsh金融机构设置 a where  ( a.`许可`='lsh' or a.`许可`='LSH' or a.`许可`=:permit  )   " +
                "   and a.金融机构=:name   )     ");
        params.put("name", name);


        return this.getList(sql.toString(), params);
    }

    public List getFinaceProductDetail(String orgi, String permit, String bianhao) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("name", orgi);
        params.put("bianhao", bianhao);
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT  DISTINCT  `金融编号`, `金融机构`,`金融机构编号`,\n" +
                "     IF ( b.`按揭类型` = '先享后选', c.`年利率`, b.`年利率` ) AS `年利率`,   \n" +
                "    b.`利率状态`,   `保证金比例`,   `最低首付比例`,   `是否尾款`,    \n" +
                "    `最高尾款比例`,     \n" +
                "    IF ( `按揭类型` = '先享后选', c.`期数`, b.`期数` ) AS `期数`,   \n" +
                "      IF ( `按揭类型` = '先享后选', c.`是否固定`, b.`是否固定` ) AS `是否固定`,\n" +
                "     c.`保值金额`,   `参数`,   `热销级别`,       " +
                "    `保值比例`,      " +
                "  `利率类型`,       " +
                "   `按揭类型`,   `产品分类`,   `贷款类型`    FROM `LSH金融设置` b    \n" +
                "   LEFT JOIN `LSH先享后选设置` c USING (`金融编号`)    \n" +
                "    LEFT JOIN `LSH金融设置适用车型` d USING (`金融编号`)    \n" +
                "    LEFT JOIN `LSH金融机构设置` e USING (`金融机构编号`)   \n" +
                "     LEFT JOIN `LSH车型` z ON d.`适用车型` = z.`车型编号`   \n" +
                "    WHERE `开始日期`<=CURDATE() AND `结束日期`>=CURDATE()    \n" +
                "             and b.`金融机构编号` =(select a.金融机构编号  from    \n" +
                "  lsh金融机构设置 a where  (   a.`许可`='LSH' or   a.`许可`=:permit  )   " +
                "   and a.金融机构=:name  )  and   b.`产品编码`=:bianhao      ");

        return this.getList(sql.toString(), params);
    }

    public List findAllFinanceFee(String bill) {
        Map params = Maps.newHashMap();
        params.put("bill", bill);

        StringBuffer sql = new StringBuffer();
        sql.append("  select a.金融   from `报价单表` a     \n" +
                "where a.初始报价单号=:bill    \n" +
                " and (a.金融 is not null or a.金融 <>'')     \n" +
                "and a.`报价编号`>=    \n" +
                "(        \n" +
                "select max(a.报价编号)  from `报价单表` a     \n" +
                "where a.初始报价单号=:bill     \n" +
                " and (a.金融 is not null or a.金融 <>'') and a.是否还原点='是'     \n" +
                "order by a.报价编号 DESC )      \n" +
                "order by a.报价编号 DESC    ");

        return this.getList(sql.toString(), params);
    }


    public Map<String, String> getFinanceIdByStarId(String id) {
        String sql = "  SELECT     b.报价编号,b.`初始报价单号`,b.`顾客编号`,b.折后价,\n" +
                "b.库存编号,b.金融 as ids,b.许可,b.`销售顾问` ,b.车型编号\n" +
                "FROM ( SELECT max(a.`报价编号`)   \n" +
                "AS 报价编号 FROM 报价单表 a\n" +
                "WHERE  \n" +
                "a.`初始报价单号`=:code  \n" +
                "AND   a.金融 IS NOT NULL   \n" +
                "GROUP BY a.`初始报价单号` ) a \n" +
                " LEFT JOIN 报价单表 b  on a.报价编号 = b.报价编号 ";
        Map params = Maps.newHashMap();
        params.put("code", id);
        List resultList = new ArrayList();
        resultList = this.getList(sql, params);
        return (Map) resultList.get(0);
    }

    public List findIsRestoreAndLoanDetail(String bill) {
        Map params = Maps.newHashMap();
        params.put("bill", bill);

        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT " +
                "  a.报价编号 AS id,    " +
                "  a.折后价  " +
                "  FROM   " +
                "  `报价单表` a       " +
                "    WHERE " +
                "  a.`报价编号` = (     " +
                "    SELECT " +
                "      max(a.报价编号)    " +
                "    FROM " +
                "      `报价单表` a    " +
                "    WHERE " +
                "      a.初始报价单号 =:bill    " +
                "    AND a.是否还原点 = '是'   " +
                "  )");

        return this.getList(sql.toString(), params);


    }


    public List getLoanClientDetailByUser(String chushi, String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
       /* params.put("chushi", chushi);*/
        StringBuffer sql = new StringBuffer();
        sql.append("  select   b.`顾客姓名` as 客户姓名 ,b.电话号码 as  客户电话,  a.客户类型,     \n" +
                "                     if(b.顾客性别='男','先生','女士') as 称谓,   \n" +
                "             h.初始报价单号,  a.报价编号, c.金融编号,     h.`操作人`,h.`更新时间`         \n" +
                "                 from    \n" +
                "                报价单表 a    \n" +
                "                JOIN `顾客记录汇总` b ON a.`顾客编号` = b.顾客编号    \n" +
                "                join 报价金融表 c on a.金融=c.金融编号   \n" +
                "                join   \n" +
                "                ( SELECT a.金融, a.`初始报价单号`, y.报价编号 FROM 报价单表 a JOIN ( SELECT max(a.`报价编号`)    \n" +
                "                             AS 报价编号 FROM 报价单表 a WHERE a.许可 =:permit     \n" +
                "                                 GROUP BY a.`初始报价单号` ) y ON a.报价编号 = y.报价编号 WHERE a.金融 IS NOT NULL       ) g    \n" +
                "                on g.报价编号=a.报价编号       \n" +
                "                join  ( select * from lsh金融申请 a where 1=1  and  (    a.状态='07' " +
                "                        or a.状态='06'  or   a.状态='08'          \n" +//  现在 是 06 就得 显示在 贷款页面
                "                        or a.状态='09' or a.状态='10' or a.状态='11' or a.状态='13'  or a.状态='14'    \n" +
                "                        or  a.撤单日期 is not null or a.撤单日期<>''   )  \n" +
                "                        or (a.`重新申请日期`<>'' and a.`重新申请日期` is not NULL   )     \n" +
                "                        or (a.`抵押日期`<>'' and a.`抵押日期` is not NULL   )   \n" +
                "                        or (a.`放款日期`<>'' and a.`放款日期` is not NULL  )   \n" +
                "                        or (a.`退款金额`<>'' and a.`退款金额` is not NULL  )   \n" +
                "                        or (a.`撤单日期`<>'' and a.`撤单日期` is not NULL)     \n" +
                "                      ) h  on h.初始报价单号=a.初始报价单号   " +
                "   ORDER BY h.`更新时间` desc       ");

        return this.getList(sql.toString(), params);

    }

    public List getRevokeInfo(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        String sql = " select * from  finance_cancel_reason as a where  a.permit=:permit";
        return this.getList(sql.toString(), params);
    }

    public List getRevokeLastUpdateInfo(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        String sql = " select  a.update_user,a.update_time  \n" +
                "  from finance_cancel_reason as a \n" +
                " where   a.update_time= ( \n" +
                "    select max(a.update_time)   \n" +
                "    from  finance_cancel_reason as a \n" +
                "    where  a.permit=:permit )";
        return this.getList(sql.toString(), params);
    }

    public List getScAndFcChatlog(FinanceService.getfinancialFailAllListitemtwothreeforss revokeInfo) {
        Map params = Maps.newHashMap();
        params.put("ids", revokeInfo.chushibaojiabianhao);
        String sql = "  select *, count(distinct 备注)   from (\n" +
                "select a.日期,d.`人员姓名` as 用户 ,a.`备注` ,a.`操作人`\n" +
                "from  detaillog a \n" +
                "left join `用户` d on d.用户名=a.更新账号\n" +
                " where a.ids=:ids  \n" +
                " and  a.备注 is not null   \n" +
                "UNION \n" +
                "  select b.`更新时间` as 日期,   \n" +
                "   d.`人员姓名` as 用户 ,b.备注,b.更新账号   \n" +
                "      from `报价金融表` b    \n" +
                "   join `用户` d on d.用户名=b.更新账号   \n" +
                "where b.金融编号 in    \n" +
                "( select c.金融 from \n" +
                "  报价单表  c \n" +
                "   where c.初始报价单号 =:ids   \n" +
                "   and c.金融 is not null\n" +
                ") and b.备注 is not null \n" +
                " ) a  where  a.备注 is not null and a.备注<>'' " +
                "  group by a.`备注`  " +
                "  ORDER BY a.`日期` desc ";
        return this.getList(sql.toString(), params);


    }

    public PageData getItemByPermitAndId(FinanceService.getfinancialFailAllListitemtwothreeforss re, String permit, String postman, int index, int size) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer();
        sql.append(" select  DISTINCT b.*,d.`人员姓名` as 所属金融专员,  g.`金融`, g.报价编号, " +
                " date_sub(DATE_ADD(jr.放款日期,INTERVAL c.贷款期数 MONTH),INTERVAL 1 day) as 贷款到期日,    "+
                "  if(b.贴息金额 is null or b.贴息金额='0' or b.贴息金额 ='','否','是' ) 是否贴息,  " +
                "  case\n" +
                "when  \n" +
                "(( c.金融机构<>'奔驰租赁' or   c.金融机构 is null)\n" +
                "and c.`金融编号` not  in(      \n" +
                "select DISTINCT 金融编号 from 报价金融详细表  ))\n" +
                "then '普通金融'\n" +
                "when c.金融机构='奔驰租赁'  then '奔驰租赁'\n" +
                "else 'annualPay' end as 金融类型," +
                "IF (ISNULL(jcb.开票时间),'否','是') 是否开票,  \n" +
                "CASE  \n" +
//                "WHEN a.`状态`='06' THEN '已申请-待批复'    \n" +
//                "WHEN a.`状态`='07' THEN '已申请-批复通过'          \n" +
//                "WHEN a.`状态`='08' THEN '已申请-批复未通过'          \n" +
//                "WHEN a.`状态`='09' THEN '通过后撤单'          \n" +
                "WHEN a.`状态`='04' THEN '通过后撤单'          \n" +
                "WHEN a.`状态`='10' THEN '完成抵押'          \n" +
//                "WHEN a.`状态`='12' THEN '新车战败'         \n" +
                "WHEN a.`状态`='11' THEN '放款到账'         \n" +
                "WHEN a.`状态`='13' THEN '撤单后重新申请'" +
//                "WHEN a.`状态`='14' THEN '条件批复'  \n" +
                "ELSE ''        \n" +
                "END AS 审批通过后状态跟踪 , \n" +
                "a.状态, \n" +
                "c.金融机构,c.`热销级别`,c.`按揭类型`," +
                "a.申请日期, \n" +
                "a.applyPassDate 通过日期, \n" +
                "a.放款日期 放款到账日期  \n" +
                "from `lsh金融申请附属` b  \n" +
                "join   ( \n" +
                "select * from  lsh金融申请 a \n" +
                "where     a.状态='07'        \n" +
                "or  a.状态='06'  or  a.状态='08' or  a.状态='04' \n" +
                "or a.状态='09' or a.状态='10' or a.状态='11' or a.状态='13' or a.状态='14' \n" +
                "or (a.`退款金额`<>'' and a.`退款金额` is not NULL  )        \n" +
                ")a  on a.初始报价单号= b.初始报价单号  \n" +
                "left join       \n" +
                "(\n" +
                "SELECT \n" +
                "b.`初始报价单号`,b.许可,  b.金融,b.`交车`,b.报价编号  \n" +
                "FROM ( SELECT max(a.`报价编号`)   \n" +
                "AS 报价编号 FROM 报价单表 a\n" +
                "WHERE a.许可 =:permit  \n" +
                "AND   a.金融 IS NOT NULL   \n" +
                "GROUP BY a.`初始报价单号` ) a \n" +
                "left JOIN 报价单表 b  on a.报价编号 = b.报价编号 \n" +
                ") g on b.初始报价单号=g.初始报价单号\n" +
                "LEFT JOIN 报价金融表 c on g.金融=c.金融编号 \n" +
                "left join lsh金融申请 jr on jr.初始报价单号 = g.初始报价单号  "+
                "LEFT JOIN 用户 d on d.`用户名`= jr.更新账号 \n" +
                " LEFT JOIN 报价交车表 jcb  on g.交车=jcb.编号  "+
                "where  g.许可 =:permit AND   g.金融 IS NOT NULL \n");
        if (re.uname != null && !"".equals(re.uname)) {
            sql.append("  and  b.贷款人姓名 like  :names  ");
            params.put("names", "%" + re.uname + "%");
        }
        if (re.utel != null && !"".equals(re.utel)) {
            sql.append("  and  b.贷款人联系电话 like  :tels  ");
            params.put("tels", "%" + re.utel + "%");
        }
        if (re.usex != null && !"".equals(re.usex)) {
            sql.append("  and  b.贷款人类型 like  :orgs  ");
            params.put("orgs", "%" + re.usex + "%");
        } else {
            sql.append("  and 1=1 ");
        }

        if (re.financialStel != null && !"".equals(re.financialStel)) {
            sql.append("  and  c.金融机构 like  :Stel  ");
            params.put("Stel", "%" + re.financialStel + "%");
        }

        if (re.newcarsolestatus != null && !"".equals(re.newcarsolestatus)) {
            if (re.newcarsolestatus.equals("通过后撤单")) {
                sql.append(" and  a.`撤单日期`<>'' and a.`撤单日期` is not NULL and a.状态='04'   ");
            } else if (re.newcarsolestatus.equals("撤单后重新申请")) {
                sql.append("  and a.`重新申请日期`<>'' and a.`重新申请日期` is not NULL and a.状态='13'    ");
            } else if (re.newcarsolestatus.equals("完成抵押")) {
                sql.append(" and  a.`抵押日期`<>'' and a.`抵押日期` is not NULL  and a.状态='10'   ");
            } else if (re.newcarsolestatus.equals("放款到账")) {
                sql.append("and  a.`放款日期`<>'' and a.`放款日期` is not NULL and a.状态='11'  ");
            } else if (re.newcarsolestatus.equals("退还服务费")) {
                sql.append("  and a.`退款金额`<>'' and a.`退款金额` is not NULL  and a.状态='04'  ");
            } else if (re.newcarsolestatus.equals("条件批复")) {
                sql.append("  and  a.状态='14'  ");
            } else {
                sql.append("  and 1=1  ");
            }
        }

        if (!StringUtils.isNullOrBlank(re.applyStartDate)) {
            sql.append(" and a.申请日期 >= :applyStartDate ");
            params.put("applyStartDate",re.applyStartDate);
        }
        if (!StringUtils.isNullOrBlank(re.applyEndDate)) {
            sql.append("and a.申请日期 <= :applyEndDate ");
            params.put("applyEndDate",re.applyEndDate);
        }
        if (!StringUtils.isNullOrBlank(re.applyPassStartDate)) {
            sql.append("and a.applyPassDate >= :applyPassStartDate ");
            params.put("applyPassStartDate",re.applyPassStartDate);
        }
        if (!StringUtils.isNullOrBlank(re.applyPassEndDate)) {
            sql.append("and a.applyPassDate <= :applyPassEndDate ");
            params.put("applyPassEndDate",re.applyPassEndDate);
        }
        if (!StringUtils.isNullOrBlank(re.loanStartDate)) {
            sql.append("and a.放款日期 >= :loanStartDate ");
            params.put("loanStartDate",re.loanStartDate);
        }
        if (!StringUtils.isNullOrBlank(re.loanEndDate)) {
            sql.append("and a.放款日期 <= :loanEndDate ");
            params.put("loanEndDate",re.loanEndDate);
        }

        sql.append(" order by a.更新时间 desc   ");
        return this.getPage(sql.toString(), params, size, index);///
    }

    public List getSingleLoanClientFinancial(String ids, String permit, String postman) {
        Map params = Maps.newHashMap();
        params.put("ids", ids);
        params.put("permit", permit);
        String sql = " \n" +
                "select  DISTINCT b.*,d.`人员姓名` as 所属金融专员 ," +
                "    a.申请结果日期, " +
                " if(b.贴息金额 is null or b.贴息金额='0' or b.贴息金额 ='','否','是' ) 是否贴息,   " +
                "CASE \n" +
                "WHEN a.`状态`='06' THEN '已申请-待批复'   \n" +
                "WHEN a.`状态`='07' THEN '已申请-批复通过'         \n" +
                "WHEN a.`状态`='08' THEN '已申请-批复未通过'         \n" +
                "WHEN a.`状态`='09' THEN '通过后撤单'         \n" +
                "WHEN a.`状态`='10' THEN '抵押完成'         \n" +
                "WHEN a.`状态`='12' THEN '新车战败'        \n" +
                "WHEN a.`状态`='11' THEN '放款到账'        \n" +
                "WHEN a.`状态`='13' THEN '撤单后重新申请'\n" +
                "WHEN a.`状态`='14' THEN '条件批复'\n" +
                "ELSE '未申请'       \n" +
                "END AS 审批通过后状态跟踪 ,\n" +
                "a.状态,\n" +
                "c.金融机构,c.`热销级别`,c.`按揭类型` \n" +
                "from `lsh金融申请附属` b \n" +
                "left join   \n" +
                "(\n" +
                "select * from  lsh金融申请 a\n" +
                "where     a.状态='07'       \n" +
                "or  a.状态='06'  or  a.状态='08'              \n" +
                "or a.状态='09' or a.状态='10' or a.状态='11' or a.状态='13'\n" +
                "  or (a.`退款金额`<>'' and a.`退款金额` is not NULL  )       \n" +
                ")a  on a.初始报价单号= b.初始报价单号 \n" +
                "LEFT JOIN 用户 d on d.`用户名`=a.更新账号\n" +
                "left join      \n" +
                "( \n" +
                "SELECT a.金融,a.许可, a.`初始报价单号`, y.报价编号  \n" +
                "FROM 报价单表 a \n" +
                "JOIN ( SELECT max(a.`报价编号`)     \n" +
                "AS 报价编号 FROM 报价单表 a WHERE a.许可 =:permit      \n" +
                "GROUP BY a.`初始报价单号` ) y ON a.报价编号 = y.报价编号\n" +
                "WHERE a.金融 IS NOT NULL   and a.许可=:permit  \n" +
                ") g on g.初始报价单号=b.初始报价单号\n" +
                "LEFT JOIN 报价金融表 c on g.金融=c.金融编号 \n" +
                " where  1=1 and g.许可=:permit and b.初始报价单号 =:ids  \n";
        return this.getList(sql.toString(), params);
    }

    public List getLoanPageOffer(String permit, FinanceService.getfinancialFailAllListitemtwothreeforss revokeInfo) {
        Map params = Maps.newHashMap();
        params.put("ids", revokeInfo.chushibaojiabianhao);
        params.put("permit", permit);
        String sql = "  select            \n" +
                "i.车架号 as VIN ,i.颜色,f.车型详细 as 车型 ,a.`折后价` as 车价  \n" +
                "from        \n" +
                "报价单表 a   \n" +
                "left join   lsh库存表 i on i.库存编号=a.库存编号   \n" +
                "left  join lsh车型 f on f.车型编号=a.车型编号    \n" +
                "left   join    \n" +
                "( SELECT a.金融, a.`初始报价单号`, y.报价编号 FROM 报价单表 a JOIN (\n" +
                "SELECT max(a.`报价编号`)   \n" +
                "AS 报价编号 FROM 报价单表 a WHERE a.许可 =:permit    \n" +
                "GROUP BY a.`初始报价单号` )\n" +
                "y ON a.报价编号 = y.报价编号 WHERE a.金融 IS NOT NULL   ) g     \n" +
                "on g.报价编号=a.报价编号        \n" +
                "where g.`初始报价单号`=:ids    ";
        return this.getList(sql.toString(), params);
    }


    public List getLoanApproveLog(String permit, FinanceService.getfinancialFailAllListitemtwothreeforss revokeInfo) {
        Map params = Maps.newHashMap();
        params.put("ids", revokeInfo.chushibaojiabianhao);
        // params.put("permit", permit);
        String sql = "   select *,count(DISTINCT a.金融机构) from  Approve_history_log  a" +
                "  where a.初始报价单号=:ids  group by a.金融机构  ";
        return this.getList(sql.toString(), params);
    }

    public List findAllRateListByorgs(String orgs, String type, String permit) {
        Map params = Maps.newHashMap();
        params.put("orgs", orgs);
        params.put("type", type);
        params.put("permit", permit);
        String sql = "   select DISTINCT a.`年利率` from  \n" +
                "(select a.金融机构编号 \n" +
                " from lsh金融机构设置 a \n" +
                "where a.金融机构=:orgs ) b \n" +
                "left join \n" +
                "lsh金融设置 a  \n" +
                " on a.`金融机构编号`=b.金融机构编号   \n" +
                "where a.`年利率`  is not null and a.年利率<>''  " +
                " and   (a.许可=:permit or a.许可='LSH')   " +
                "  AND a.年利率<>'0'  \n" +
                " and a.利率状态=:type   ";
        return this.getList(sql.toString(), params);


    }

    public List getFinanceRateAndCarId(String oid, String permit, List<String> listStr) {
        Map params = Maps.newHashMap();
        params.put("oid", oid);
        params.put("permit", permit);
        String sql = "   SELECT   b.报价编号,b.`初始报价单号`,b.`顾客编号`,b.折后价, c.标配MSRP含税, \n" +
                "b.库存编号,b.金融,b.许可,b.`销售顾问` ,b.车型编号 \n" +
                "FROM ( SELECT max(a.`报价编号`)    \n" +
                "AS 报价编号 FROM 报价单表 a \n" +
                "WHERE   \n" +
                "a.`初始报价单号`=:oid  and   \n" +
                "a.许可 =:permit  \n" +
                "AND   a.金融 IS NOT NULL    \n" +
                "GROUP BY a.`初始报价单号` ) a  \n" +
                ", 报价单表 b, lsh库存表 c " +
                " where  a.报价编号 = b.报价编号 and b.库存编号 = c.库存编号 ";
        return this.getList(sql.toString(), params);

    }

    public List getBenzYearRateList(FinanceService.getCounter re, String permit, List<String> listStr) {
        Map params = new HashMap();
        List relsit = new ArrayList();
        if (Strings.isNullOrEmpty(re.qishu) && !Strings.isNullOrEmpty(re.heTongSum)) {
            // 参数 不为空
            params.put("oid", re.heTongSum);
            params.put("type", re.prodectType);
            params.put("carId", re.carprice);
            params.put("modelSource", listStr.get(0));
            params.put("modelBrand", listStr.get(1));
            params.put("modelClass", listStr.get(2));
            params.put("modelStyle", listStr.get(3));
            params.put("modelDetail", listStr.get(4));
            params.put("permit", permit);
            String sql = "select \n" +
                    " DISTINCT c.期数 id1   \n" +
                    "from `LSH先享后选设置` c \n" +
                    " join \n" +
                    "(\n" +
                    "select DISTINCT a.金融编号 ,a.适用车型  from \n" +
                    " LSH金融设置适用车型 a \n" +
                    "  \n" +
                    ") b  on c.`金融编号`=b.`金融编号` left join lsh车型 e on  b.`适用车型` = e.`车型编号`   \n" +
                    "left JOIN (\n" +
                    "select * from\n" +
                    "LSH金融设置  WHERE (`许可` = :permit OR `许可` = 'LSH')\n" +
                    ") a     on a.金融编号=b.金融编号\n" +
                    "left  JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号 \n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE() \n" +
                    " and d.金融机构='奔驰租赁'  and `按揭类型` = '先享后选' \n" +
                    "and  (\n" +
                    "             e.`车型编号` = :carId     \n" +
                    "            OR (\n" +
                    "                e.来源 = :modelSource \n" +
                    "                AND e.品牌 = :modelBrand \n" +
                    "                AND e.级别 = :modelClass  \n" +
                    "                AND e.车型 = :modelStyle  \n" +
                    "                AND e.车型详细 = :modelDetail  \n" +
                    "            )     \n" +
                    "            OR (\n" +
                    "                e.来源 = :modelSource \n" +
                    "                AND e.品牌 = :modelBrand \n" +
                    "                AND e.级别 = :modelClass  \n" +
                    "                AND e.车型 = :modelStyle\n" +
                    "                AND e.车型详细 = '' \n" +
                    "            )     \n" +
                    "            OR (\n" +
                    "                 e.来源 = :modelSource \n" +
                    "                AND e.品牌 = :modelBrand \n" +
                    "                AND e.级别 = :modelClass \n" +
                    "                AND e.车型 = '' \n" +
                    "                AND e.车型详细 = '' \n" +
                    "            )   \n" +
                    "            OR (\n" +
                    "                e.来源 = :modelSource \n" +
                    "                AND e.品牌 = :modelBrand \n" +
                    "                AND e.级别 = '' \n" +
                    "                AND e.车型 = '' \n" +
                    "                AND e.车型详细 = '' \n" +
                    "            )  \n" +
                    "            OR (\n" +
                    "                e.来源 = :modelSource  \n" +
                    "                AND e.品牌 = '' \n" +
                    "                AND e.级别 = '' \n" +
                    "                AND e.车型 = '' \n" +
                    "                AND e.车型详细 = '' \n" +
                    "            )  \n" +
                    "        )  \n" +
                    "         and  c.参数=:oid and c.利率类型=:type ";

            return this.getList(sql, params);
        }
        if (!Strings.isNullOrEmpty(re.qishu) && Strings.isNullOrEmpty(re.heTongSum)) {
            params.put("oid", re.qishu);
            params.put("type", re.prodectType);
            params.put("carId", re.carprice);
            params.put("modelSource", listStr.get(0));
            params.put("modelBrand", listStr.get(1));
            params.put("modelClass", listStr.get(2));
            params.put("modelStyle", listStr.get(3));
            params.put("modelDetail", listStr.get(4));
            params.put("permit", permit);
            String sql = "select \n" +
                    "DISTINCT  c.参数 id1   \n" +
                    "from `LSH先享后选设置` c\n" +
                    "  join \n" +
                    "(\n" +
                    "select DISTINCT a.金融编号  ,a.适用车型   from \n" +
                    " LSH金融设置适用车型 a \n" +
                    ") b  on c.`金融编号`=b.`金融编号`\n" +
                    " left join lsh车型 e on  b.`适用车型` = e.`车型编号`   \n" +
                    "left JOIN (\n" +
                    "select * from\n" +
                    "LSH金融设置  WHERE (`许可` = :permit OR `许可` = 'LSH')\n" +
                    ") a     on a.金融编号=b.金融编号\n" +
                    "left   JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号\n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE() \n" +
                    " and d.金融机构='奔驰租赁'  and `按揭类型` = '先享后选'  and \n" +
                    "(\n" +
                    "  e.`车型编号` = :carId    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    ")  \n" +
                    " and  c.期数=:oid and c.利率类型=:type ";
            return this.getList(sql.toString(), params);
        }
        if (!Strings.isNullOrEmpty(re.qishu) && !Strings.isNullOrEmpty(re.heTongSum)) {
            params.put("oid", re.qishu);
            params.put("ooid", re.heTongSum);
            params.put("type", re.prodectType);
            params.put("carId", re.carprice);
            params.put("modelSource", listStr.get(0));
            params.put("modelBrand", listStr.get(1));
            params.put("modelClass", listStr.get(2));
            params.put("modelStyle", listStr.get(3));
            params.put("modelDetail", listStr.get(4));
            params.put("permit", permit);
            String sql = "select \n" +
                    " DISTINCT c.年利率 id1    \n" +
                    "from `LSH先享后选设置` c\n" +
                    "  join \n" +
                    "(\n" +
                    "select DISTINCT a.金融编号  ,a.适用车型   from \n" +
                    " LSH金融设置适用车型 a \n" +
                    ") b  on c.`金融编号`=b.`金融编号`\n" +
                    " left join lsh车型 e on  b.`适用车型` = e.`车型编号`   \n" +
                    " left JOIN (\n" +
                    "select * from\n" +
                    "LSH金融设置  WHERE (`许可` = :permit OR `许可` = 'LSH')\n" +
                    ") a     on a.金融编号=b.金融编号\n" +
                    "LEFT JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号\n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE() \n" +
                    " and d.金融机构='奔驰租赁'  and `按揭类型` = '先享后选'  and  \n" +
                    "( \n" +
                    "  e.`车型编号` = :carId    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    ")  \n" +
                    " and  c.期数=:oid and c.参数=:ooid  and c.利率类型=:type ";
            return this.getList(sql, params);

        }
        return relsit;
    }


    public List getBenzCaseSecond(FinanceService.getCounter re, String permit, List<String> listStr) {
        Map params = Maps.newHashMap();
        params.put("oid", re.qishu);
        params.put("ooid", re.heTongSum);
        params.put("type", re.prodectType);
        params.put("carId", re.carprice);
        params.put("permit", permit);
        params.put("yearrate", re.yearRate);
        params.put("modelSource", listStr.get(0));
        params.put("modelBrand", listStr.get(1));
        params.put("modelClass", listStr.get(2));
        params.put("modelStyle", listStr.get(3));
        params.put("modelDetail", listStr.get(4));
        String sql = "select \n" +
                " a.金融编号 financeId," +
                " a.产品编码 productId," +
                " c.年利率 yearRate," +
                "if(c.保值比例 is null,0,c.保值比例)  mValue, \n" +
                "if(a.保证金比例 is null,0,a.保证金比例)  gValue  \n" +
                "from `LSH先享后选设置` c\n" +
                " join \n" +
                "(\n" +
                "select DISTINCT a.金融编号  ,a.适用车型   from \n" +
                " LSH金融设置适用车型 a \n" +
                "  \n" +
                ") b  on c.`金融编号`=b.`金融编号`" +
                " left join lsh车型 e on  b.`适用车型` = e.`车型编号` \n" +
                "left JOIN (\n" +
                "select * from\n" +
                "LSH金融设置  WHERE (`许可` = :permit OR `许可` = 'LSH')\n" +
                ") a     on a.金融编号=b.金融编号\n" +
                " JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号\n" +
                "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE() \n" +
                " and d.金融机构='奔驰租赁'  and `按揭类型` = '先享后选' \n" +
                " and  c.期数=:oid and c.参数=:ooid  " +
                "and c.利率类型=:type and c.年利率=:yearrate  and " +
                "(\n" +
                " e.`车型编号` = :carId    \n" +
                "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                ")  \n" +
                " limit 1";
        return this.getList(sql.toString(), params);
    }

    public List getFinanceOrgsAndYearRateList(String permit, String postman,
                                              FinanceService.getCounter re, List<String> listStr) {
        List reList = new ArrayList();
        Map param = new HashMap();
        String sql = "";
        if (Strings.isNullOrEmpty(re.orgs) && Strings.isNullOrEmpty(re.yearRate)) {
            sql = " select  \n" +
                    "  DISTINCT a.年利率  AS id1 \n" +
                    "from  ( \n" +
                    "select * from \n" +
                    "LSH金融设置  WHERE (`许可` = :permit OR `许可` = 'LSH') \n" +
                    ") a  \n" +
                    "left join  \n" +
                    "( \n" +
                    "select DISTINCT a.金融编号  ,a.适用车型   from  \n" +
                    "LSH金融设置适用车型 a  \n" +
                    ") b  on a.`金融编号`=b.`金融编号`\n" +
                    "left join lsh车型 e on  b.`适用车型` = e.`车型编号`\n" +
                    "left JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号 \n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE() " +
                    " and a.年利率 is not null and a.年利率<>'' and a.年利率<>0  and  \n" +
                    "(\n" +
                    "  e.`车型编号` = :carId    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    ")  \n";
            param.put("permit", permit);
            param.put("carId", re.carprice);
            param.put("modelSource", listStr.get(0));
            param.put("modelBrand", listStr.get(1));
            param.put("modelClass", listStr.get(2));
            param.put("modelStyle", listStr.get(3));
            param.put("modelDetail", listStr.get(4));

        }

        if (Strings.isNullOrEmpty(re.orgs) && !Strings.isNullOrEmpty(re.yearRate)) {
            sql = " select DISTINCT 金融机构 from (  select  \n" +
                    " DISTINCT  a.年利率 AS id1   \n" +
                    "from  ( \n" +
                    "select * from \n" +
                    "LSH金融设置  WHERE (`许可` = :permit OR `许可` = 'LSH') \n" +
                    ") a  \n" +
                    "left join  \n" +
                    "( \n" +
                    "select DISTINCT a.金融编号,a.适用车型    from  \n" +
                    "LSH金融设置适用车型 a  \n" +
                    ") b  on a.`金融编号`=b.`金融编号`\n" +
                    "left join lsh车型 e on  b.`适用车型` = e.`车型编号`\n" +
                    "left JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号 \n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE()      \n" +
                    "and a.年利率 is not null and a.年利率<>'' and a.年利率<>0 and       " +
                    "(\n" +
                    "  e.`车型编号` = :carId    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    ")  ) a \n" +
                    "WHERE a.id1=:yearRate   ";

            param.put("permit", permit);
            param.put("yearRate", re.yearRate);
            param.put("carId", re.carprice);
            param.put("modelSource", listStr.get(0));
            param.put("modelBrand", listStr.get(1));
            param.put("modelClass", listStr.get(2));
            param.put("modelStyle", listStr.get(3));
            param.put("modelDetail", listStr.get(4));
        }

        if (!Strings.isNullOrEmpty(re.orgs) && Strings.isNullOrEmpty(re.yearRate)) {
            sql = " select DISTINCT 年利率 from (  select  \n" +
                    " DISTINCT  d.金融机构 AS id1  ,a.年利率  \n" +
                    "from  ( \n" +
                    "select * from \n" +
                    "LSH金融设置  WHERE (`许可` = :permit OR `许可` = 'LSH') \n" +
                    ") a  \n" +
                    "left join  \n" +
                    "( \n" +
                    "select DISTINCT a.金融编号,a.适用车型    from  \n" +
                    "LSH金融设置适用车型 a  \n" +
                    ") b  on a.`金融编号`=b.`金融编号`\n" +
                    "left join lsh车型 e on  b.`适用车型` = e.`车型编号`\n" +
                    "left JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号 \n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE()      \n" +
                    "and a.年利率 is not null and a.年利率<>'' and a.年利率<>0 and       " +
                    "(\n" +
                    "  e.`车型编号` = :carId    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    ") " +
                    " ) a \n" +
                    "WHERE a.id1=:orgs   ";

            param.put("permit", permit);
            param.put("orgs", re.orgs);
            param.put("carId", re.carprice);
            param.put("modelSource", listStr.get(0));
            param.put("modelBrand", listStr.get(1));
            param.put("modelClass", listStr.get(2));
            param.put("modelStyle", listStr.get(3));
            param.put("modelDetail", listStr.get(4));
        }
        return this.getList(sql, param);
    }

    public boolean getIsBelongToOrgs(String permit, String postman,
                                     FinanceService.getCounter re, List<String> listStr) {
        boolean flag = true;
        // 判断 这个车型编号 对应的  能不能 转换金融方案
        String sql = "";
        Map param = new HashMap();
        if (re.type.equals("1")) {
            sql = "     select   \n" +
                    "DISTINCT  d.金融机构 AS id1  ,a.年利率   \n" +
                    "from  (  \n" +
                    "select * from  \n" +
                    "LSH金融设置  WHERE ( 许可=:permit or  `许可` = 'LSH')  \n" +
                    ") a   \n" +
                    "left join   \n" +
                    "(  \n" +
                    "select DISTINCT a.金融编号,a.适用车型    from   \n" +
                    "LSH金融设置适用车型 a   \n" +
                    ") b  on a.`金融编号`=b.`金融编号` \n" +
                    "left join lsh车型 e on  b.`适用车型` = e.`车型编号` \n" +
                    "left JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号  \n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE()       \n" +
                    "and a.年利率 is not null and a.年利率<>'' and a.年利率<>0 " +
                    " and(\n" +
                    "  e.`车型编号` = :carId    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    ") ";

        } else {
            sql = "    select  \n" +
                    "c.`年利率`,c.`参数`\n" +
                    "from `LSH先享后选设置` c \n" +
                    "join  \n" +
                    "( \n" +
                    "select DISTINCT a.金融编号  ,a.适用车型   from  \n" +
                    "LSH金融设置适用车型 a  \n" +
                    "\n" +
                    ") b  on c.`金融编号`=b.`金融编号` \n" +
                    "left join lsh车型 e on  b.`适用车型` = e.`车型编号`  \n" +
                    "left JOIN ( \n" +
                    "select * from \n" +
                    "LSH金融设置  WHERE (   许可=:permit or `许可` = 'LSH') \n" +
                    ") a     on a.金融编号=b.金融编号 \n" +
                    "JOIN lsh金融机构设置 d on d.`金融机构编号`=a.金融机构编号 \n" +
                    "WHERE a.`开始日期`<=CURDATE() AND a.`结束日期`>=CURDATE()  \n" +
                    "and d.金融机构='奔驰租赁'  and `按揭类型` = '先享后选'   \n" +
                    "and c.`年利率` is not null and c.`年利率`<>''\n" +
                    "and c.`参数` is not null and c.`参数`<>'' " +
                    " and(\n" +
                    "  e.`车型编号` = :carId    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = :modelDetail )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = :modelStyle AND e.车型详细 = '' )    \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = :modelClass AND e.车型 = '' AND e.车型详细 = '' )  \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = :modelBrand AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    "OR ( e.来源 = :modelSource AND e.品牌 = '' AND e.级别 = '' AND e.车型 = '' AND e.车型详细 = '' ) \n" +
                    ") ";
        }
        param.put("carId", re.carprice);
        param.put("permit", permit);
        param.put("modelSource", listStr.get(0));
        param.put("modelBrand", listStr.get(1));
        param.put("modelClass", listStr.get(2));
        param.put("modelStyle", listStr.get(3));
        param.put("modelDetail", listStr.get(4));
        List flagLIst = new ArrayList();
        flagLIst = this.getList(sql, param);
        if (flagLIst != null && flagLIst.size() > 0) {
            flag = true;
        } else {
            flag = false;
        }
        return flag;
    }

    @Override
    public String getLoanPeriods(String permit, String StartQuotationNumber) {
        String sql = "select b.`贷款期数` \n" +
                "from `报价单表` a\n" +
                "LEFT JOIN `报价金融表` b \n" +
                "ON a.`金融` = b.`金融编号` \n" +
                "where a.`初始报价单号` = :StartQuotationNumber and a.`许可` = :permit  and a.`是否驳回` <>'是' ORDER BY a.报价编号 desc";
        Map param = new HashMap();
        param.put("permit",permit);
        param.put("StartQuotationNumber",StartQuotationNumber);
        List list = this.getList(sql,param);
        Map map = Maps.newHashMap();
        if(list!=null&&list.size()>0){
            map = (Map)list.get(0);
        }
        return map.containsKey("贷款期数")?map.get("贷款期数").toString():null;
    }


    @Override
    public List getFinanceSettings(String permit, String carId, String modelSource, String modelBrand, String modelClass,
                                   String modelStyle, String modelDetail, String companyId, String yearRate,
                                   String loanterm, String downRate, String balRate, String type) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  ");
        if ("1".equals(type)) {
            sb.append(" 	`金融机构`, ");
            sb.append(" 	`金融机构编号` ");
        } else if ("2".equals(type)) {
            sb.append(" 	`期数` ");
        } else if ("3".equals(type)) {
            sb.append(" 	`利率状态`, ");
            sb.append(" 	`年利率` ");
        } else if ("4".equals(type)) {
            sb.append(" 	`最低首付比例` ");
        } else if ("5".equals(type)) {
            sb.append(" 	ifnull(`最高尾款比例`, 0) 最高尾款比例 ");
        }
        sb.append(" from ( ");
        sb.append(getFinanceSettingsSQL());
        sb.append(" ) `lsh金融设置` ");
        sb.append(" where 1 = 1 ");

        Map params = new HashMap();
        params.put("permit", permit);
        params.put("carId", carId);
        params.put("modelSource", modelSource);
        params.put("modelBrand", modelBrand);
        params.put("modelClass", modelClass);
        params.put("modelStyle", modelStyle);
        params.put("modelDetail", modelDetail);

        if (companyId != null && !"".equals(companyId)) {
            sb.append(" and `lsh金融设置`.`金融机构编号` = :companyId ");
            params.put("companyId", companyId);
        }
        if (loanterm != null && !"".equals(loanterm)) {
            sb.append(" and `lsh金融设置`.`期数` = :loanterm ");
            params.put("loanterm", loanterm);
        }
        if (yearRate != null && !"".equals(yearRate)) {
            sb.append(" and `lsh金融设置`.`年利率` = :yearRate ");
            params.put("yearRate", yearRate);
        }
        if (downRate != null && !"".equals(downRate)) {
            sb.append(" and `lsh金融设置`.`最低首付比例` <= :downRate ");
            params.put("downRate", downRate);
        }
        if (balRate != null && !"".equals(balRate)) {
            sb.append(" and IFNULL(`lsh金融设置`.`最高尾款比例`, 0) >= :balRate ");
            params.put("balRate", balRate);
        }
        if ("1".equals(type)) {
            sb.append(" AND `lsh金融设置`.`金融机构编号` <> '' AND `lsh金融设置`.`金融机构编号` IS NOT NULL ");
            sb.append(" group by `lsh金融设置`.`金融机构编号` order by `lsh金融设置`.`金融机构编号` ");
        } else if ("2".equals(type)) {
            sb.append(" AND `lsh金融设置`.`期数` <> '' AND `lsh金融设置`.`期数` IS NOT NULL ");
            sb.append(" group by `lsh金融设置`.`期数` order by `lsh金融设置`.`期数` ");
        } else if ("3".equals(type)) {
            sb.append(" AND `lsh金融设置`.`年利率` <> '' AND `lsh金融设置`.`年利率` IS NOT NULL ");
            sb.append(" AND `lsh金融设置`.`利率状态` <> '' AND `lsh金融设置`.`利率状态` IS NOT NULL ");
            sb.append(" group by `lsh金融设置`.`年利率` order by `lsh金融设置`.`年利率` ");
        } else if ("4".equals(type)) {
            sb.append(" AND `lsh金融设置`.`最低首付比例` <> '' AND `lsh金融设置`.`最低首付比例` IS NOT NULL ");
            sb.append(" group by `lsh金融设置`.`最低首付比例` order by `lsh金融设置`.`最低首付比例` ");
        } else if ("5".equals(type)) {
            //sb.append(" AND `lsh金融设置`.`最高尾款比例` IS NOT NULL ");
            sb.append(" group by `lsh金融设置`.`最高尾款比例` order by `lsh金融设置`.`最高尾款比例` DESC ");
        }
        return this.getList(sb.toString(), params);
    }

    @Override
    public List getFinanceSettingsInfo(String permit, String carId, String modelSource, String modelBrand,
                                       String modelClass, String modelStyle, String modelDetail, String companyId,
                                       String yearRate, String loanterm, String downRate, String balRate) {
        StringBuffer sb = new StringBuffer();
        sb.append(getFinanceSettingsSQL());
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("carId", carId);
        params.put("modelSource", modelSource);
        params.put("modelBrand", modelBrand);
        params.put("modelClass", modelClass);
        params.put("modelStyle", modelStyle);
        params.put("modelDetail", modelDetail);
        if (companyId != null && !"".equals(companyId)) {
            sb.append(" and `LSH金融机构设置`.`金融机构编号` = :companyId ");
            params.put("companyId", companyId);
        }
        if (loanterm != null && !"".equals(loanterm)) {
            sb.append(" and `lsh金融设置`.`期数` = :loanterm ");
            params.put("loanterm", loanterm);
        }
        if (yearRate != null && !"".equals(yearRate)) {
            sb.append(" and `lsh金融设置`.`年利率` = :yearRate ");
            params.put("yearRate", yearRate);
        }
        if (downRate != null && !"".equals(downRate)) {
            sb.append(" and `lsh金融设置`.`最低首付比例` <= :downRate ");
            params.put("downRate", downRate);
        }
        if (balRate != null && !"".equals(balRate)) {
            sb.append(" and IFNULL(`lsh金融设置`.`最高尾款比例`, 0) >= :balRate ");
            params.put("balRate", balRate);
        }
        return this.getList(sb.toString(), params);
    }

    private String getFinanceSettingsSQL() {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  ");
        sb.append(" 	`LSH金融机构设置`.`金融机构`, ");
        sb.append(" 	`LSH金融机构设置`.`金融机构编号`, ");
        sb.append(" 	`金融编号`, ");
        sb.append(" 	`年利率`, ");
        sb.append(" 	`利率状态`, ");
        sb.append(" 	`保证金比例`, ");
        sb.append(" 	`最低首付比例`, ");
        sb.append(" 	`是否尾款`, ");
        sb.append(" 	`最高尾款比例`, ");
        sb.append(" 	`期数`, ");
        sb.append(" 	`是否固定`, ");
        sb.append(" 	`热销级别`, ");
        sb.append(" 	`业务类型`, ");
        sb.append(" 	`按揭类型`, ");
        sb.append(" 	`产品分类`, ");
        sb.append(" 	`产品编码`, ");
        sb.append(" 	`贷款类型` ");
        sb.append(" FROM ");
        sb.append(" 	( ");
        sb.append(" 		SELECT * FROM `lsh金融设置` ");
        sb.append(" 		WHERE (`许可` = :permit OR `许可` = 'LSH') ");
        sb.append(" 	) `lsh金融设置` ");
        sb.append(" LEFT JOIN `LSH金融设置适用车型` USING (`金融编号`) ");
        sb.append(" LEFT JOIN `LSH车型` ON `LSH金融设置适用车型`.`适用车型` = `LSH车型`.`车型编号` ");
        sb.append(" LEFT JOIN `LSH金融机构设置` USING (`金融机构编号`) ");
        sb.append(" WHERE `开始日期` <= CURDATE() ");
        sb.append(" AND `结束日期` >= CURDATE() ");
        //sb.append(" AND `按揭类型` <> '先享后选' ");
        sb.append(" AND `LSH金融机构设置`.`状态` = '启用' ");
        sb.append(" AND ( `LSH车型`.`车型编号` = '0'   ");
        sb.append(" 	OR `LSH车型`.`车型编号` = :carId   ");
        sb.append(" 	OR ( `LSH车型`.来源 = :modelSource AND `LSH车型`.品牌 = :modelBrand AND `LSH车型`.级别 = :modelClass AND `LSH车型`.车型 = :modelStyle AND `LSH车型`.车型详细 = :modelDetail )   ");
        sb.append(" 	OR ( `LSH车型`.来源 = :modelSource AND `LSH车型`.品牌 = :modelBrand AND `LSH车型`.级别 = :modelClass AND `LSH车型`.车型 = :modelStyle AND `LSH车型`.车型详细 = '' )   ");
        sb.append(" 	OR ( `LSH车型`.来源 = :modelSource AND `LSH车型`.品牌 = :modelBrand AND `LSH车型`.级别 = :modelClass AND `LSH车型`.车型 = '' AND `LSH车型`.车型详细 = '' ) ");
        sb.append(" 	OR ( `LSH车型`.来源 = :modelSource AND `LSH车型`.品牌 = :modelBrand AND `LSH车型`.级别 = '' AND `LSH车型`.车型 = '' AND `LSH车型`.车型详细 = '' ) ");
        sb.append(" 	OR ( `LSH车型`.来源 = :modelSource AND `LSH车型`.品牌 = '' AND `LSH车型`.级别 = '' AND `LSH车型`.车型 = '' AND `LSH车型`.车型详细 = '' ) ) ");
        return sb.toString();
    }


    public List getLastReplace(String permit) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select a.`更新时间` updateTime,ifnull(a.`更新账号`, '') updateAccount from `lsh金融机构手续费` a " +
                " JOIN `lsh金融机构设置` b ON a.`金融机构编号` = b.`金融机构编号` " +
                " WHERE b.`许可` = :permit " +
                " ORDER BY a.`更新时间` DESC " +
                " LIMIT 0,1 ");
        Map map = new HashMap();
        map.put("permit", permit);
        return getList(sb.toString(), map);
    }

    @Override
    public PageData exportFinanceAccountInfo(String permit, String cusName, String cusTel, String cusType, String companyName, String state, int pageSize, int pageIndex) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        param.put("permit", permit);
        sb.append(" SELECT " +
                " 	l.`人员姓名` 所属金融专员, " +
                " 	e.贷款人类型, " +
                " 	e.贷款人姓名, " +
                " 	e.贷款人身份证 贷款人身份证号, " +
                " 	e.贷款人联系电话 贷款人联系方式, " +
                " 	e.联系人姓名, " +
                " 	e.联系人身份证 联系人身份证号, " +
                " 	e.联系人联系电话 联系人联系方式, " +
                " 	n.value 新车销售状态, " +
                " 	a.车型 意向车型, " +
                " 	a.车型详细 意向车款, " +
                " 	h.车架号, " +
                " 	a.折后价 销售价格, " +
                " 	i.经销商名 零售经销商, " +
                " 	j.人员姓名 销售顾问, " +
                " 	a.热销级别, " +
                " 	a.按揭类型, " +
                " 	a.金融机构, " +
                " 	c.金融产品编码, " +
                " 	case " +
                " 	when ((a.金融机构 <> '奔驰租赁' or a.金融机构 is null) and a.`金融编号` not in (select DISTINCT 金融编号 from 报价金融详细表)) then '普通金融' " +
                " 	when a.金融机构 = '奔驰租赁' then '奔驰租赁' " +
                " 	else 'annualPay' end as 金融类型, " +
                " 	if(a.利率状态='利率',if(a.金融利率='' or a.金融利率 is null ,0, a.金融利率),0) as 利率, " +
                " 	if(a.利率状态='费率',if(a.金融利率='' or a.金融利率 is null ,0, a.金融利率),0) as 费率, " +
                " 	a.贷款类型 贷款类型, " +
                " 	a.贷款额 贷款金额, " +
                " 	a.贷款期数 月数, " +
                " 	a.月付额 月供, " +
                " 	a.保值参数 年公里数, " +
                " 	a.保值比例 保值比例, " +
                " 	a.保值金额 保值金额, " +
                " 	a.保证金比例 保证金比例, " +
                " 	a.保证金金额 保证金金额, " +
                " 	a.修养套餐金额, " +
                " 	a.首付比例 首付比例, " +
                " 	a.首付额 首付金额, " +
                " 	a.`尾款比例` 尾款比例, " +
                " 	a.`尾款额` 尾款金额, " +
                " 	a.`金融服务费` 金融服务费, " +
                " 	a.`金融手续费` 金融手续费, " +
                " 	'' 金融销售状态, " +
                " 	'' 审批通过后状态跟踪, " +
                " 	DATE_FORMAT(a.开票时间,'%Y-%m-%d') 开票日期, " +
                " 	DATE_FORMAT(a.退票时间,'%Y-%m-%d') 退票日期, " +
                " 	'' 放款到账日, " +
                " 	date_sub(DATE_ADD(a.放款日期,INTERVAL a.贷款期数 MONTH),INTERVAL 1 day) 贷款到期日 " +
                " FROM ( " +
                " 	SELECT " +
                " 		a.`许可`, " +
                " 		a.`折后价`, " +
                " 		a.`销售顾问`, " +
                " 		a.`顾客编号`, " +
                " 		a.`初始报价单号`, " +
                " 		a.`报价编号`, " +
                " 		a.`车型编号`, " +
                " 		a.`库存编号`, " +
                " 		a.`金融`, " +
                " 		a.`开票时间`, " +
                " 		a.`退票时间`, " +
                " 		b.金融机构, " +
                " 		b.金融编号, " +
                " 		b.热销级别, " +
                " 		b.按揭类型, " +
                " 		b.利率状态, " +
                " 		b.金融利率, " +
                " 		b.贷款类型, " +
                " 		b.贷款额, " +
                " 		b.贷款期数, " +
                " 		b.月付额, " +
                " 		b.保值比例, " +
                " 		b.保值金额, " +
                " 		b.保证金比例, " +
                " 		b.保证金金额, " +
                " 		b.修养套餐金额, " +
                " 		b.首付比例, " +
                " 		b.首付额, " +
                " 		b.`尾款比例`, " +
                " 		b.`尾款额`, " +
                " 		b.`金融服务费`, " +
                " 		b.`金融手续费`, " +
                " 		b.`保值参数`, " +
                " 		c.状态, " +
                " 		c.放款日期, " +
                " 		c.更新账号, " +
                " 		d.车型, " +
                " 		d.车型详细 " +
                " 	FROM vw_retailed_quotation a, 报价金融表 b, LSH金融申请 c, LSH车型 d " +
                " 	WHERE a.`金融` = b.`金融编号` AND a.初始报价单号 = c.初始报价单号 and a.车型编号 = d.车型编号 " +
                " 	AND a.许可 = :permit " +
                " 	AND C.状态 NOT IN ('04','09') " +
                " 	AND B.金融机构 IS NOT NULL " +
                " 	AND B.贷款额 > 0 " +
                " ) a " +
                " JOIN `lsh金融申请附属` e ON a.初始报价单号 = e.初始报价单号 " +
                " LEFT JOIN `顾客记录汇总` f on a.顾客编号 = f.顾客编号 " +
                " LEFT JOIN lsh库存表 h ON a.库存编号 = h.库存编号 " +
                " LEFT JOIN `lsh经销商表` i ON a.许可 = i.许可_BMBS " +
                " LEFT JOIN `用户` j ON a.销售顾问 = j.用户名 " +
                " LEFT JOIN `用户` l ON a.`更新账号` = l.`用户名` " +
                " LEFT JOIN car_finance_state m ON a.`状态` = m.`key` " +
                " LEFT JOIN car_sales_state n ON f.新车销售状态 = n.`key` " +
                " where 1 = 1 ");
        if (!Strings.isNullOrEmpty(cusName)) {
            sb.append(" and e.贷款人姓名 = :cusName ");
            param.put("cusName", cusName);
        }
        if (!Strings.isNullOrEmpty(cusTel)) {
            sb.append(" and e.贷款人联系电话 = :cusTel ");
            param.put("cusTel", cusTel);
        }
        if (!Strings.isNullOrEmpty(cusType)) {
            sb.append(" and e.贷款人类型 = :cusType ");
            param.put("cusType", cusType);
        }
        if (!Strings.isNullOrEmpty(companyName)) {
            sb.append(" and a.金融机构 = :companyName ");
            param.put("companyName", companyName);
        }
        if (!Strings.isNullOrEmpty(state)) {
            if (state.equals("通过后撤单")) {
                sb.append(" and a.状态 = '09'   ");
            } else if (state.equals("撤单后重新申请")) {
                sb.append(" and a.状态 = '13'    ");
            } else if (state.equals("完成抵押")) {
                sb.append(" and a.状态 = '10'   ");
            } else if (state.equals("放款到账")) {
                sb.append(" and a.状态 = '11'  ");
            } else if (state.equals("退还服务费")) {
                sb.append(" and a.状态 = '04'  ");
            } else if (state.equals("条件批复")) {
                sb.append("  and  a.状态 = '14'  ");
            }
        }
        return this.getPage(sb.toString(), param, pageSize, pageIndex);

    }

    public PageData exportFinanceLoanInfo(String permit, String cusName, String cusTel, String cusType,
                                          String companyName, String state, int pageSize, int pageIndex,
                                          String applyStartDate,String applyEndDate,String applyPassStartDate,String applyPassEndDate,String loanStartDate,String loanEndDate) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer();
        sql.append(" select  DISTINCT b.*,d.`人员姓名` as 所属金融专员,  g.`金融`, " +
                " date_sub(DATE_ADD(jr.放款日期,INTERVAL c.贷款期数 MONTH),INTERVAL 1 day) as 贷款到期日,    "+
                "  if(b.贴息金额 is null or b.贴息金额='0' or b.贴息金额 ='','否','是' ) 是否贴息,  " +
                "  case\n" +
                "when  \n" +
                "(( c.金融机构<>'奔驰租赁' or   c.金融机构 is null)\n" +
                "and c.`金融编号` not  in(      \n" +
                "select DISTINCT 金融编号 from 报价金融详细表  ))\n" +
                "then '普通金融'\n" +
                "when c.金融机构='奔驰租赁'  then '奔驰租赁'\n" +
                "else 'annualPay' end as 金融类型," +
                "case\n" +
                "when  g.交车 is  NULL then  '否'\n" +
                "when  \n" +
                " (g.交车 is not null\n" +
                "    and EXISTS( select a.开票时间  from \n" +
                "   `报价交车表` a where g.交车=a.编号  ) \n" +
                "  ) then '是'\n" +
                "end as 是否开票 ,  \n" +
//                "CASE  \n" +
//                "WHEN a.`状态`='06' THEN '已申请-待批复'    \n" +
//                "WHEN a.`状态`='07' THEN '已申请-批复通过'          \n" +
//                "WHEN a.`状态`='08' THEN '已申请-批复未通过'          \n" +
//                "WHEN a.`状态`='09' THEN '通过后撤单'          \n" +
//                "WHEN a.`状态`='10' THEN '抵押完成'          \n" +
//                "WHEN a.`状态`='12' THEN '新车战败'         \n" +
//                "WHEN a.`状态`='11' THEN '放款到账'         \n" +
//                "WHEN a.`状态`='13' THEN '撤单后重新申请'" +
//                "WHEN a.`状态`='14' THEN '条件批复'  \n" +
//                "ELSE '未申请'        \n" +
//                "END AS 审批通过后状态跟踪 , \n" +
                "CASE  \n" +
//                "WHEN a.`状态`='06' THEN '已申请-待批复'    \n" +
//                "WHEN a.`状态`='07' THEN '已申请-批复通过'          \n" +
//                "WHEN a.`状态`='08' THEN '已申请-批复未通过'          \n" +
//                "WHEN a.`状态`='09' THEN '通过后撤单'          \n" +
                "WHEN a.`状态`='04' THEN '通过后撤单'          \n" +
                "WHEN a.`状态`='10' THEN '完成抵押'          \n" +
//                "WHEN a.`状态`='12' THEN '新车战败'         \n" +
                "WHEN a.`状态`='11' THEN '放款到账'         \n" +
                "WHEN a.`状态`='13' THEN '撤单后重新申请'" +
//                "WHEN a.`状态`='14' THEN '条件批复'  \n" +
                "ELSE ''        \n" +
                "END AS 审批通过后状态跟踪 , \n" +
                "a.状态, \n" +
                "c.金融机构,c.`热销级别`,c.`按揭类型`,   \n" +
                "a.申请日期, \n" +
                "a.applyPassDate 通过日期, \n" +
                "a.放款日期 放款到账日期  \n" +
                "from `lsh金融申请附属` b  \n" +
                "left join   ( \n" +
                "select * from  lsh金融申请 a \n" +
                "where     a.状态='07'        \n" +
                "or  a.状态='06'  or  a.状态='08' or  a.状态='04' \n" +
                "or a.状态='09' or a.状态='10' or a.状态='11' or a.状态='13' or a.状态='14' \n" +
                "or (a.`退款金额`<>'' and a.`退款金额` is not NULL  )        \n" +
                ")a  on a.初始报价单号= b.初始报价单号  \n" +
                "LEFT JOIN 用户 d on d.`用户名`=a.更新账号 \n" +
                "left join       \n" +
                "(\n" +
                "SELECT \n" +
                "b.`初始报价单号`,b.许可,  b.金融,b.`交车`  \n" +
                "FROM ( SELECT max(a.`报价编号`)   \n" +
                "AS 报价编号 FROM 报价单表 a\n" +
                "WHERE a.许可 =:permit  \n" +
                "AND   a.金融 IS NOT NULL   \n" +
                "GROUP BY a.`初始报价单号` ) a \n" +
                "left JOIN 报价单表 b  on a.报价编号 = b.报价编号 \n" +
                ") g on b.初始报价单号=g.初始报价单号\n" +
                "LEFT JOIN 报价金融表 c on g.金融=c.金融编号 \n" +
                "left join lsh金融申请 jr on jr.初始报价单号 = g.初始报价单号  "+
                "where  g.许可 =:permit AND   g.金融 IS NOT NULL \n");
        if (cusName != null && !"".equals(cusName)) {
            sql.append("  and  b.贷款人姓名 like  :names  ");
            params.put("names", "%" + cusName + "%");
        }
        if (cusTel != null && !"".equals(cusTel)) {
            sql.append("  and  b.贷款人联系电话 like  :tels  ");
            params.put("tels", "%" + cusTel + "%");
        }
        if (cusType != null && !"".equals(cusType)) {
            sql.append("  and  b.贷款人类型 like  :orgs  ");
            params.put("orgs", "%" + cusType + "%");
        }

        if (companyName != null && !"".equals(companyName)) {
            sql.append("  and  c.金融机构 like  :Stel  ");
            params.put("Stel", "%" + companyName + "%");
        }

        if (state != null && !"".equals(state)) {
            if (state.equals("通过后撤单")) {
                sql.append(" and  a.`撤单日期`<>'' and a.`撤单日期` is not NULL and a.状态='04'   ");
            } else if (state.equals("撤单后重新申请")) {
                sql.append("  and a.`重新申请日期`<>'' and a.`重新申请日期` is not NULL and a.状态='13'    ");
            } else if (state.equals("完成抵押")) {
                sql.append(" and  a.`抵押日期`<>'' and a.`抵押日期` is not NULL  and a.状态='10'   ");
            } else if (state.equals("放款到账")) {
                sql.append("and  a.`放款日期`<>'' and a.`放款日期` is not NULL and a.状态='11'  ");
            } else if (state.equals("退还服务费")) {
                sql.append("  and a.`退款金额`<>'' and a.`退款金额` is not NULL  and a.状态='04'  ");
            } else if (state.equals("条件批复")) {
                sql.append("  and  a.状态='14'  ");
            }
        }

        if (!StringUtils.isNullOrBlank(applyStartDate)) {
            sql.append(" and a.申请日期 >= :applyStartDate ");
            params.put("applyStartDate",applyStartDate);
        }
        if (!StringUtils.isNullOrBlank(applyEndDate)) {
            sql.append("and a.申请日期 <= :applyEndDate ");
            params.put("applyEndDate",applyEndDate);
        }
        if (!StringUtils.isNullOrBlank(applyPassStartDate)) {
            sql.append("and a.applyPassDate >= :applyPassStartDate ");
            params.put("applyPassStartDate",applyPassStartDate);
        }
        if (!StringUtils.isNullOrBlank(applyPassEndDate)) {
            sql.append("and a.applyPassDate <= :applyPassEndDate ");
            params.put("applyPassEndDate",applyPassEndDate);
        }
        if (!StringUtils.isNullOrBlank(loanStartDate)) {
            sql.append("and a.放款日期 >= :loanStartDate ");
            params.put("loanStartDate",loanStartDate);
        }
        if (!StringUtils.isNullOrBlank(loanEndDate)) {
            sql.append("and a.放款日期 <= :loanEndDate ");
            params.put("loanEndDate",loanEndDate);
        }

        sql.append(" order by a.更新时间 desc   ");
        return this.getPage(sql.toString(), params, pageSize, pageIndex);
    }

    public PageData exportFinanceAccountInfoOverWrite(String permit, String cusName, String cusTel, String cusType, String companyName, String state, int pageSize, int pageIndex,
                                                      String applyStartDate,String applyEndDate,String applyPassStartDate,String applyPassEndDate,String loanStartDate,String loanEndDate) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        param.put("permit", permit);
        sb.append("  select " +
                " 	l.`人员姓名` 所属金融专员, " +
                " 	b.贷款人类型, " +
                " 	b.贷款人姓名, " +
                " 	b.贷款人身份证 贷款人身份证号, " +
                " 	b.贷款人联系电话 贷款人联系方式, " +
                " 	b.联系人姓名, " +
                " 	b.联系人身份证 联系人身份证号, " +
                " 	b.联系人联系电话 联系人联系方式, " +
                " 	n.value 新车销售状态, " +
                " 	o.车型 意向车型, " +
                " 	o.车型详细 意向车款, " +
                " 	h.车架号, " +
                " 	g.折后价 销售价格, " +
                " 	i.经销商名 零售经销商, " +
                " 	j.人员姓名 销售顾问, " +
                " 	c.热销级别, " +
                " 	c.按揭类型, " +
                " 	c.金融机构, " +
                " 	c.金融产品编码, " +
                " 	case " +
                " 	when ((c.金融机构 <> '奔驰租赁' or c.金融机构 is null) and c.`金融编号` not in (select DISTINCT 金融编号 from 报价金融详细表)) then '普通金融' " +
                " 	when c.金融机构 = '奔驰租赁' then '奔驰租赁' " +
                " 	else 'annualPay' end as 金融类型, " +
                " 	if(c.利率状态='利率',if(c.金融利率='' or c.金融利率 is null ,0, c.金融利率),0) as 利率, " +
                " 	if(c.利率状态='费率',if(c.金融利率='' or c.金融利率 is null ,0, c.金融利率),0) as 费率, " +
                " 	c.贷款类型 贷款类型, " +
                " 	c.贷款额 贷款金额, " +
                " 	c.贷款期数 月数, " +
                " 	c.月付额 月供, " +
                " 	c.保值参数 年公里数, " +
                " 	c.保值比例 保值比例, " +
                " 	c.保值金额 保值金额, " +
                " 	c.保证金比例 保证金比例, " +
                " 	c.保证金金额 保证金金额, " +
                " 	c.修养套餐金额, " +
                " 	c.首付比例 首付比例, " +
                " 	c.首付额 首付金额, " +
                " 	c.`尾款比例` 尾款比例, " +
                " 	c.`尾款额` 尾款金额, " +
                " 	c.`金融服务费` 金融服务费, " +
                " 	c.`金融手续费` 金融手续费, " +
                " 	if(jr.状态 in ('06','07','08','09','10','11','12','13','14'), m.`value`, '未申请' ) 金融销售状态, " +
                "CASE  \n" +
//                "WHEN a.`状态`='06' THEN '已申请-待批复'    \n" +
//                "WHEN a.`状态`='07' THEN '已申请-批复通过'          \n" +
//                "WHEN a.`状态`='08' THEN '已申请-批复未通过'          \n" +
//                "WHEN a.`状态`='09' THEN '通过后撤单'          \n" +
                "WHEN a.`状态`='04' THEN '通过后撤单'          \n" +
                "WHEN a.`状态`='10' THEN '完成抵押'          \n" +
//                "WHEN a.`状态`='12' THEN '新车战败'         \n" +
                "WHEN a.`状态`='11' THEN '放款到账'         \n" +
                "WHEN a.`状态`='13' THEN '撤单后重新申请'" +
//                "WHEN a.`状态`='14' THEN '条件批复'  \n" +
                "ELSE ''        \n" +
                "END AS 审批通过后状态跟踪 , \n" +
//                " 	m.`value` 审批通过后状态跟踪, " +
                " 	DATE_FORMAT(p.开票时间,'%Y-%m-%d') 开票日期, " +
                " 	DATE_FORMAT(p.退票时间,'%Y-%m-%d') 退票日期, " +
                " 	jr.放款日期 放款到账日, " +
                " 	date_sub(DATE_ADD(jr.放款日期,INTERVAL c.贷款期数 MONTH),INTERVAL 1 day) 贷款到期日, " +
                "a.申请日期, \n" +
                "a.applyPassDate 通过日期, \n" +
                "a.放款日期 放款到账日期  \n" +
                " from `lsh金融申请附属` b   " +
                "  join (  " +
                " 	select * from lsh金融申请 a  " +
                " 	where a.状态 in ('07','06','08','04','09','10','11','13','14') " +
                " 	or (a.`退款金额`<>'' and a.`退款金额` is not NULL) " +
                " ) a on a.初始报价单号= b.初始报价单号 " +
                " LEFT JOIN 用户 d on d.`用户名` = a.更新账号 " +
                " left join ( " +
                " 	SELECT  " +
                " 		b.* " +
                " 	FROM ( " +
                " 		SELECT " +
                " 			max(a.`报价编号`) AS 报价编号 FROM 报价单表 a " +
                " 		WHERE a.许可 = :permit " +
                " 		AND a.金融 IS NOT NULL " +
                " 		GROUP BY a.`初始报价单号` " +
                " 	) a " +
                " 	left JOIN 报价单表 b  on a.报价编号 = b.报价编号 " +
                " ) g on b.初始报价单号 = g.初始报价单号 " +
                " LEFT JOIN 报价金融表 c on g.金融 = c.金融编号 " +
                " left join lsh金融申请 jr on jr.初始报价单号 = g.初始报价单号 " +
                " LEFT JOIN `顾客记录汇总` f on g.顾客编号 = f.顾客编号 " +
                " LEFT JOIN lsh库存表 h ON g.库存编号 = h.库存编号 " +
                " LEFT JOIN `lsh经销商表` i ON g.许可 = i.许可_BMBS " +
                " LEFT JOIN `用户` j ON g.销售顾问 = j.用户名 " +
                " LEFT JOIN `用户` l ON a.`更新账号` = l.`用户名` " +
                " LEFT JOIN car_finance_state m ON jr.`状态` = m.`key` " +
                " LEFT JOIN car_sales_state n ON f.新车销售状态 = n.`key` " +
                " LEFT JOIN lsh车型 o ON g.车型编号 = o.车型编号 " +
                " LEFT JOIN `报价交车表` p ON g.交车 = p.`编号` " +
                " where  g.许可 = :permit  " +
                " AND g.金融 IS NOT NULL ");
        if (!Strings.isNullOrEmpty(cusName)) {
            sb.append(" and b.贷款人姓名 like :cusName ");
            param.put("cusName", "%" + cusName + "%");
        }
        if (!Strings.isNullOrEmpty(cusTel)) {
            sb.append(" and b.贷款人联系电话 like :cusTel ");
            param.put("cusTel", "%" + cusTel + "%");
        }
        if (!Strings.isNullOrEmpty(cusType)) {
            sb.append(" and b.贷款人类型 like :cusType ");
            param.put("cusType", "%" + cusType + "%");
        }
        if (!Strings.isNullOrEmpty(companyName)) {
            sb.append(" and c.金融机构 like :companyName ");
            param.put("companyName", "%" + companyName + "%");
        }
        if (!Strings.isNullOrEmpty(state)) {
            if (state.equals("通过后撤单")) {
                sb.append(" and a.状态 = '04' and  a.`撤单日期`<>'' and a.`撤单日期` is not NULL  ");
            } else if (state.equals("撤单后重新申请")) {
                sb.append(" and a.状态 = '13' and a.`重新申请日期`<>'' and a.`重新申请日期` is not NULL   ");
            } else if (state.equals("完成抵押")) {
                sb.append(" and a.状态 = '10' and  a.`抵押日期`<>'' and a.`抵押日期` is not NULL   ");
            } else if (state.equals("放款到账")) {
                sb.append(" and a.状态 = '11' and  a.`放款日期`<>'' and a.`放款日期` is not NULL ");
            } else if (state.equals("退还服务费")) {
                sb.append(" and a.状态 = '04' and a.`退款金额`<>'' and a.`退款金额` is not NULL  ");
            } else if (state.equals("条件批复")) {
                sb.append("  and  a.状态 = '14'  ");
            }
        }

        if (!StringUtils.isNullOrBlank(applyStartDate)) {
            sb.append(" and a.申请日期 >= :applyStartDate ");
            param.put("applyStartDate",applyStartDate);
        }
        if (!StringUtils.isNullOrBlank(applyEndDate)) {
            sb.append("and a.申请日期 <= :applyEndDate ");
            param.put("applyEndDate",applyEndDate);
        }
        if (!StringUtils.isNullOrBlank(applyPassStartDate)) {
            sb.append("and a.applyPassDate >= :applyPassStartDate ");
            param.put("applyPassStartDate",applyPassStartDate);
        }
        if (!StringUtils.isNullOrBlank(applyPassEndDate)) {
            sb.append("and a.applyPassDate <= :applyPassEndDate ");
            param.put("applyPassEndDate",applyPassEndDate);
        }
        if (!StringUtils.isNullOrBlank(loanStartDate)) {
            sb.append("and a.放款日期 >= :loanStartDate ");
            param.put("loanStartDate",loanStartDate);
        }
        if (!StringUtils.isNullOrBlank(loanEndDate)) {
            sb.append("and a.放款日期 <= :loanEndDate ");
            param.put("loanEndDate", loanEndDate);
        }
        sb.append(" order by a.更新时间 desc   ");
        return this.getPage(sb.toString(), param, pageSize, pageIndex);

    }

    @Override
    public List getFinanceProduceNum(String permit, String companyId1, String companyId2, String carId, String origin, String brank, String grade, String carType, String carTypeDetail) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        param.put("permit", permit);
        sb.append(" SELECT " +
                " 	DISTINCT c.`产品编码` " +
                " FROM " +
                " 	lsh金融设置 c " +
                " WHERE (c.`许可` = 'LSH' OR c.`许可` = :permit) ");
        if (!StringUtils.isNullOrBlank(companyId1) && !StringUtils.isNullOrBlank(companyId2)) {
            sb.append("  and (c.金融机构编号 = :companyId1 or c.金融机构编号 = :companyId2 ) ");
            param.put("companyId1", companyId1);
            param.put("companyId2", companyId2);
        } else if (!StringUtils.isNullOrBlank(companyId1)) {
            sb.append("  and c.金融机构编号 = :companyId1 ");
            param.put("companyId1", companyId1);
        } else if (!StringUtils.isNullOrBlank(companyId2)) {
            sb.append("  and c.金融机构编号 = :companyId2  ");
            param.put("companyId2", companyId2);
        }

        sb.append(" AND c.`开始日期` <= CURDATE() " +
                " AND c.`结束日期` >= CURDATE() " +
                " AND c.`产品编码` <> '' " +
                " AND c.`产品编码` IS NOT NULL " +
                " AND c.金融编号 IN ( " +
                " 		SELECT " +
                " 			b.金融编号 " +
                " 		FROM " +
                " 			LSH金融设置适用车型 b " +
                " 		WHERE " +
                " 			b.适用车型 IN ( " +
                " 				SELECT " +
                " 					a.车型编号 " +
                " 				FROM " +
                " 					lsh车型 a " +
                " 				WHERE a.`车型编号` = '0'   " +
                "         	OR a.`车型编号` = :carId   " +
                "         	OR ( a.来源 = :modelSource AND a.品牌 = :modelBrand AND a.级别 = :modelClass AND a.车型 = :modelStyle AND a.车型详细 = :modelDetail )   " +
                "         	OR ( a.来源 = :modelSource AND a.品牌 = :modelBrand AND a.级别 = :modelClass AND a.车型 = :modelStyle AND a.车型详细 = '' )   " +
                "         	OR ( a.来源 = :modelSource AND a.品牌 = :modelBrand AND a.级别 = :modelClass AND a.车型 = '' AND a.车型详细 = '' ) " +
                "         	OR ( a.来源 = :modelSource AND a.品牌 = :modelBrand AND a.级别 = '' AND a.车型 = '' AND a.车型详细 = '' ) " +
                "         	OR ( a.来源 = :modelSource AND a.品牌 = '' AND a.级别 = '' AND a.车型 = '' AND a.车型详细 = '' ) " +
                " 			) " +
                " 	) ");
        param.put("carId", carId);
        param.put("modelSource", origin);
        param.put("modelBrand", brank);
        param.put("modelClass", grade);
        param.put("modelStyle", carType);
        param.put("modelDetail", carTypeDetail);
        return this.getList(sb.toString(), param);
    }

    @Override
    public List getFinanceFee(String companyId, String financeFeeType) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        param.put("companyId", companyId);
        sb.append("select * from LSH金融机构手续费 a where a.金融机构编号 = :companyId ");
        if (financeFeeType != null && !"".equals(financeFeeType)) {
            sb.append(" and a.分类 = :financeFeeType");
            param.put("financeFeeType", financeFeeType);
        }
        return this.getList(sb.toString(), param);
    }

    public PageData getSuitableArea(){
        String sql = "select p.id,p.area_name from group_area p where p.parent_id = 1 and p.status = 1";
        Map param = new HashMap();
        return this.getPage(sql, param, 0, 0);
    }

    public PageData getSuitableStore(Integer id){
        String sql = "select p.id,p.area_name from group_area p where p.parent_id = :id and p.status = 1";
        Map param = new HashMap();
        param.put("id",id);
        return this.getPage(sql, param, 0, 0);
    }

    @Override
    public List getFinDefeatedReson(){
        String sqlStr="select `a`.`金融战败原因编号` AS `金融战败原因编号`,`a`.`战败原因` AS `战败原因`, a.启用状态 as 状态 from (`lsh金融战败原因` `a` join `lsh金融战败原因附属` `b` on((`a`.`金融战败原因编号` = `b`.`金融战败原因编号`))) where ((`a`.`许可` = 'LSH') and (`b`.`战败分类` = '金融战败')) GROUP BY `a`.`金融战败原因编号`";
        return this.getList(sqlStr);
    }

    @Override
    public PageData getFinanceInfoByName(String name){
        String sqlStr = "select " +
                "   a.金融机构编号," +
                "   a.金融机构," +
                "   a.brand 品牌," +
                "   a.许可," +
                "   a.状态," +
                "   a.type," +
                "   a.applicable_area 区域许可," +
                "   a.applicable_store 经销店许可 ," +
                "   b.dealer_name 区域," +
                "   b.id 区域编号," +
                "   c.id 经销店编号," +
                "   c.dealer_name 经销店 " +
                "   from lsh金融机构设置 a " +
                "   LEFT JOIN group_area b ON a.applicable_area = b.permit " +
                "   LEFT JOIN group_area c ON a.applicable_store = c.permit " +
                "   where a.金融机构 = :name";
        Map param = Maps.newHashMap();
        param.put("name",name);
        return this.getPage(sqlStr,param,0,0);
    }

    @Override
    public List getAreaAndStorefoById(Integer id){
        String sqlStr = "select a.金融机构编号 , a.brand,b.permit 区域许可,c.permit 经销店许可,b.id 区域编号,c.id 经销店编号  " +
                "   from lsh金融机构设置 a " +
                "   LEFT JOIN group_area b ON a.applicable_area = b.permit  " +
                "   LEFT JOIN group_area c ON a.applicable_store = c.permit  " +
                "   where 金融机构编号 = "+id ;
        return this.getList(sqlStr);
    }

}

